How can I add a single character to a duplicate cell in Excel

[laundon]

Hi,

I have used this forum many times over the years for help and hope this time, you can help again!

To explain, I have a spreadsheet of part numbers or product codes. Each part number originally had a prefix, say, AA-1234. I had the need to remove the "AA-" part. This then gives me a column with a part number without a prefix, however I may now have duplicates as AA-1234 and BB-1234 will both now be 1234.

What I am trying to do is on the duplicate line only, add a suffix character to make it unique again and I can't work out how to do it!

So to continue my example from above, the duplicate line only would then become 1234D for example making it now unique, so I would have 1234, and 1234D

Any ideas from you fabulous bunch of people?

Many Thanks, and Festive Greetings to you all!

Tim.

 

[izzyjurss]

Sorry A6 should just be "Diur tric"

 

[bebo021999]

1) Is the column A in sequence ?
2) "Diur tricB" to become "Diur tric1":
why not "Diur tricB"?
edit: ignore it. I had posted before the correction in 11 seen.

 

[bebo021999]

Book1
	A	B
1
2	Cymb cana	Cymb cana
3	Cymb cana	Cymb cana1
4	Cymb cana	Cymb cana2
5	Diur tric	Diur tric
6	Diur tric	Diur tric1
7	Geno plumn	Geno plumn
8	Geno plumn	Geno plumn1
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=A2&IF(COUNTIF($A$1:A2,A2)=1,"",COUNTIF($A$1:A2,A2)-1)

 

[Peter_SSs]

This is a sample of my input and desired output

From your image it appears that the Input data is sorted. If that is so, then this should suffice.

22 05 17.xlsm
	A	B
1	Input	Output
2	Cymb cana	Cymb cana
3	Cymb cana	Cymb cana1
4	Cymb cana	Cymb cana2
5	Diur tric	Diur tric
6	Diur tric	Diur tric1
7	Geno plum	Geno plum
8	Geno rhyo	Geno rhyo
9	Geno supe	Geno supe
Add count

Cell Formulas
Range	Formula
B2:B9	B2	=A2&IF(A2=A1,COUNTIF(A$1:A1,A2),"")