Help needed with numbering duplicates in a spreadsheet!!

Alex_Vu

New Member
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!

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.

Last edited:

Replies
12
Views
341
Replies
21
Views
838
Replies
4
Views
82
Replies
5
Views
100
Replies
16
Views
433

1,207,165
Messages
6,076,899
Members
446,237
Latest member
ruinedelf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back