Help needed with numbering duplicates in a spreadsheet!!

[Alex_Vu]

I've been stuck at a crossroads with how to create a formula assigning values sequentially, but repetitively for duplicates, in my spreadsheet.


To illustrate this, I have:


A
B
B
B
C
C
D
D


I want to assign the n-values as follows:


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


Note: The numbers would be created in Column A and the duplicate values I would be trying to "detect" would be in Column H.


Any help at all would be appreciated!! Thanks!

 

[Fishboy]

Hi Alex_Vu, welcome to the boards.

Using your example, and assuming the letters start in A1 and your number values will start in B1, add a row above your data. In the new blank B1 put a zero. Now in B2 add the following formula:

=IF(A2<>A1,B1+1,B1)

Now drag-fill this down column B. It should check:

If the value in column A is not the same as the cell above it, the number will increment by from the number in the cell above. If the value in column A is the same as the cell above it, the number will not increment by one. This will only work properly if column A is sorted as it is in your example.