CODEWITHSUNDEEP
excelduplicatesserial-number
user4542333
user4542333

Reputation:

I want to give same number to the duplicate data in excel

I want to give same number to the duplicate data in excel

 A      B
apple   1
apple   1
apple   1
ball    2
bat     3
dog     4
dog     4
goat    5

Upvotes: 0

Views: 7758

Answers (2)

philosov
philosov

Reputation: 1

It is also possible to do it in a dynamic way that does not require sorting.

Paste the following formula in B2 and copy down the column:

=IFERROR(INDEX(B$1:B1,MATCH(A2,A$1:A1,0)),MAX(B$1:B1)+1)

Upvotes: 0

dimitris_ps
dimitris_ps

Reputation: 5951

Sort your column A then insert the number 1 in cell B2 and the following function in cell B3 and copy down.

in cell B2=1

function in cell B3: =if(A3=A2, B2, B2+1)

Update

enter image description here

Upvotes: 2

Related Questions