Incrementing Duplicates

[nosferer]

Hello there

How do I scan a column for duplicates and increment each duplicate by one..i.e. if the text AAA and BBB occurs a number of times in a single column how do I scan that column and change the text to AAA001 AAA002 BBB001 etc

 

[Aladin Akyurek]

Lets assume that the data (AAA,BBB, etc) start at row 2 in A. Row 1 has a label in A1 and B1 empty.

In B2 enter and copy down:

=A2&TEXT(IF(COUNTIF($B$1:B1,A2&"*")=0,1,COUNTIF($B$1:B1,A2&"*")+1),"000")

Aladin