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),"")

 

[MartaGH]

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),"")

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)

I just solved my problem with your solution to this question... I hope you are still around and that this message gets to you... just wanted to say Thank you!!! Truly appreciate your taking the time to answer questions that will help not only the person that asks but many more that will come in the future to learn from you.

Thank you!

 

[Peter_SSs]

I just solved my problem with your solution to this question... I hope you are still around and that this message gets to you... just wanted to say Thank you!!! Truly appreciate your taking the time to answer questions that will help not only the person that asks but many more that will come in the future to learn from you.

Thank you!

You are welcome. Glad you found the thread useful. Thanks for your comments.

BTW, if you might ask questions in the future, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MartaGH]

You are welcome. Glad you found the thread useful. Thanks for your comments.

BTW, if you might ask questions in the future, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done

 

[Peter_SSs]

Done

Thanks for that.