Creating unique ID numbers for duplicate Excel rows

Question

I have an Excel spreadsheet with multiple columns. I'd like to automatically add unique ID numbers (starting in cell A2) to duplicate values in column D (starting at D2). Any way to make the spreadsheet look like below? Thanks.

Column A     Column D
1               3
1               3
2             Bard
2             Bard
3             4ton
3             4ton
3             4ton

Solution 1

In A1 enter 1

In A2 enter:

=IF(D1=D2,A1,A1+1)

and copy down.

Solution 2

It's not clear on whether your data table has column header labels or not but this formula should not make a difference beyond having to pre-populate A1 if there is no column header label.

In A2 use this formula,

=IFERROR(INDEX(A$1:A1, MATCH(D2, D$1:D1, 0)), MAX(A$1:A1)+1)

Fill down as necessary. Your results should resemble the following.

        

While your column D was sorted, that is not a requirement with this formula.