Results 1 to 7 of 7

Thread: Numbered List Of Unique Values

  1. #1

    Numbered List Of Unique Values

    Hello Everyone. I have been working a long tim on this and I actually think it may be one of those things that cannot be done by formula or Sub Routine. I hope I explain what I want to do properly ...

    I need to itemise a list of values but I only want to go up a number when the cell value is unique when compared to the cells above ie a range. An exmple of what I need visually is..

    Column1 | Column 2
    1 | 3504
    1 | 3504
    2 | 6401
    3 | 1205
    1 | 3504
    2 | 6401

    So I need the formula to automate the numbers in Column 1 dependant on the value of Column 2.

    Each number is given an item number starting from 1 of course. I guess it would be like a rowcount that ignores the duplicates.

    I hope this makes sense any help is gratefully apprciated. Thanks

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Write 1 in A1, and in A2 use =IF(ISNUMBER(MATCH(B2,B$1:B1,0)),INDEX(A$1:A1,MATC H(B2,B$1:B1,0)),MAX(A$1:A1)+1) and drag down
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Thanks Admin. Think it needs a slight tweek as its not displaying the correct item number in colum A. I am attaching the source file. Can you take a look and advise. Many thanks Attachment 570

  4. #4
    Sorry I was being a fool. Forgot source file starts row 2 and I didnt adjust the forula. Now sorted, thanks very much for your help.

  5. #5
    Actually there is a problem. There is a circular ref

  6. #6
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    Quote Originally Posted by xander1981 View Post
    Actually there is a problem. There is a circular ref
    Hi,

    In Excel Fox formula is a typo but made from place where is formula, use this :

    =IF(ISNUMBER(MATCH(B2,B$1:B1,0)),INDEX(A$1:A1,MATCH(B2,B$1:B1,0)),MAX(A$1:A1)+1)

    Your date must start in B2 and in A2 use Excel Fox formula , for me works .

  7. #7
    Perfect! thanks guys. I get a little rubbush using multi functions within a formula.

Similar Threads

  1. Extract Unique Values List
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 4
    Last Post: 03-06-2012, 09:51 PM
  2. List Unique Values Using Formula
    By LalitPandey87 in forum Excel Help
    Replies: 5
    Last Post: 01-09-2012, 08:39 PM
  3. Replies: 2
    Last Post: 01-07-2012, 12:11 AM
  4. Unique Large Values From Duplicate List
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 10-04-2011, 02:17 AM
  5. List Unique/Common Values From Two Ranges
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 09-16-2011, 08:34 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •