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
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
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.
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
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
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.
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
My apologies, I made a new thread.
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?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks