Incrementing Duplicates

nosferer · May 2nd, 2002, 07:42 AM

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 · May 2nd, 2002, 08:04 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 07:42 AM	#1
nosferer New Member Join Date: May 2002 Posts: 1	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

May 2nd, 2002, 08:04 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	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