+ Reply to Thread
Results 1 to 9 of 9

Assign sequential numbers to unique values in a list

  1. #1
    Forum Contributor
    Join Date
    04-11-2008
    Posts
    156

    Assign sequential numbers to unique values in a list

    Hi,

    Please can you help me assign a sequential number to each new value in an unsorted list, and where duplicates occur, assign the same number as the duplicate E.g.

    A 1
    B 2
    C 3
    D 4
    A 1
    C 3
    E 5

    Thank you

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Assign sequential numbers to unique values in a list

    Assuming your data starts in A2, (leaving a row above), then in B2 enter:

    =IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$1:B1,2,0))

    copied down.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    12-03-2014
    Location
    Oceanside, CA
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Assign sequential numbers to unique values in a list

    What about assigning unique values with multiple criteria, for instance:

    aa 1
    ab 2
    aa 1
    ba 1
    bb 2
    bc 3
    ca 1
    ca 1
    cb 2
    cc 3

    I am assigning unique values for each of the subsets (first letter is a, b, c). I am able to separate out the two parts in the first column like this:

    a a 1
    a b 2
    a a 1
    b a 1
    b b 2
    b c 3
    c a 1
    c a 1
    c b 2
    c c 3

  4. #4
    Registered User
    Join Date
    12-03-2014
    Location
    Oceanside, CA
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Assign sequential numbers to unique values in a list

    I found the answer to my question. I was able to do it with the array formula as follows:

    {=IF(COUNTIFS(A2:A$2,A2,B2:B$2,B2)>1,VLOOKUP(C2,C1:D$1,2,0),MAX(IF(A1:A$1=A2,D1:D$1)+1))}

    Where column A is the first set, column B is a set within each subset in column A, column C is column A values concatenated with column B, and column D is the unique value within each subset of column A generated by the array formula. Remember to copy the array formula without the brackets around it and commit the array formula to the cell with CTRL+Shift+Enter.


    a a aa 1
    a b ab 2
    a a aa 1
    b a ba 1
    b b bb 2
    b c bc 3
    c a ca 1
    c a ca 1
    c b cb 2
    c c cc 3
    c b cb 2
    a c ac 3
    a b ab 2
    a a aa 1
    a a aa 1
    a b ab 2
    a a aa 1
    b a ba 1
    b b bb 2
    b c bc 3
    c a ca 1
    c a ca 1
    c b cb 2
    c c cc 3
    c b cb 2
    a c ac 3
    a b ab 2
    a a aa 1

  5. #5
    Registered User
    Join Date
    09-15-2016
    Location
    Lakeland, FL
    MS-Off Ver
    Windows 7 Professional
    Posts
    4

    Re: Assign sequential numbers to unique values in a list

    I want to achieve a similar objective, but when I insert the formula, it will not insert a number to a respective duplicate value.

    After inserting the formula =if(COUNTIF(H$2:H2,H2)=1,MAX(I$1:I1)+1,VLOOKUP(H2,H$1:I1,0,FALSE))

    It returns:

    H~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~I
    Heat#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Blank)
    HT 414... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~1
    HT D07... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2
    HT C21... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~3
    HT 414... Duplicate of first Unique Value~~~~~~~~~~~~~~~~(Blank)
    HT 234... Unique~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~4

    My question is how do I get the duplicates to show the value given to the original unique value so that, in this example, the second and subsequent HT 414... will show 1 and so on for all possible duplicates.

    For instance, if HT C21 reappears, the adjacent cell will return "3"

    Thank you in advance.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,389

    Re: Assign sequential numbers to unique values in a list

    jmshanahan welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    09-15-2016
    Location
    Lakeland, FL
    MS-Off Ver
    Windows 7 Professional
    Posts
    4

    Re: Assign sequential numbers to unique values in a list

    My apologies, I made a new thread.

  8. #8
    Registered User
    Join Date
    09-30-2017
    Location
    Afghanistan
    MS-Off Ver
    365
    Posts
    1

    Re: Assign sequential numbers to unique values in a list

    Thank you this thread has proven to be useful. How can the formula that was provided to the OP be modified to ignore '0' as a unique entry?
    =IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$1:B1,2,0))

    A 1
    B 2
    C 3
    D 4
    A 1
    C 3
    E 5
    0 Blank/Nil
    0 Blank/Nil
    0 Blank/Nil
    0 Blank/Nil (or just resolve to 0)

    The reason I am asking is because my field is populated from another sheet using the =INDIRECT("Sheet!A1") which resolves 0 until the referenced cell is populated. I know it's one question per thread but I think this is related closely enough?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,389

    Re: Assign sequential numbers to unique values in a list

    Cottonbaler welcome to the forum

    Perhaps you missed my post #6 a little above your post?
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1