Assign sequential numbers to unique values in a list

1. 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. 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.

3. 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. 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. 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"

6. 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.

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.

8. 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. 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.

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.

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

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