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.

 

[bebo021999]

How many duplicate would be for 1234? Could you pls post screenshot of actual data?

 

[bebo021999]

add "D", "E" ... for each duplicate item (not excess 24 )

Book1
	A	B
1	CC-1234	1234
2	AA-1234	1234D
3	AA-5678	5678
4	BB-1234	1234E
Sheet1

Cell Formulas
Range	Formula
B1	B1	=RIGHT(A2,LEN(A2)-SEARCH("-",A2))
B2:B4	B2	=RIGHT(A2,LEN(A2)-SEARCH("-",A2))&IF(COUNTIF($A$1:A1,"*"&RIGHT(A2,LEN(A2)-SEARCH("-",A2))),CHAR(COUNTIF($A$1:A1,"*"&RIGHT(A2,LEN(A2)-SEARCH("-",A2)))+67),"")

 

[laundon]

How many duplicate would be for 1234? Could you pls post screenshot of actual data?

There is going to be possibly no more than 5 duplicates maximum I would have thought.

Basically, I have a product list in Excel that I'm trying to import into some software, The product codes need to be unique. In the spreadsheet, the prefix is a supplier code and I I need the list without supplier code. This then gives me the duplicates.

I can't share the file unfortunately as it's customer data.

 

[kweaver]

Maybe something like this?

Book1
	A	B	C
1	AA-1234	1234	1234A
2	BB-1234	1234	1234B
3	XX-5555	5555	5555A
4	YY-1245	1245	1245
5	CC-9876	9876	9876
6	XX-5555	5555	5555B
7	tt-1234	1234	1234C
Sheet3

Cell Formulas
Range	Formula
B1:B7	B1	=RIGHT(A1,4)
C1:C7	C1	=B1&IF(COUNTIF(B:B,B1)>=2,MID("ABCDEFGHIJK",COUNTIF($B$1:B1,B1),1),"")

 

[laundon]

Many thanks for the replies so far.

I already have a cell with the prefix removed.

All I need to do now is add something to the end of the duplicate entry to make it unique and leave the other instance for the number untouched, so I'm left with 1234, and 1234D for example.

 

[Peter_SSs]

My interpretation

21 12 14.xlsm
	B	C
1	1234	1234
2	1234	1234B
3	5555	5555
4	1245	1245
5	9876	9876
6	5555	5555B
7	1234	1234C
Mark Dupes

Cell Formulas
Range	Formula
C1:C7	C1	=B1&SUBSTITUTE(CHAR(64+COUNTIF(B$1:B1,B1)),"A","")

Or if you want the first dupe suffix to be "D", then

=B1&SUBSTITUTE(CHAR(66+COUNTIF(B$1:B1,B1)),"C","")

 

[izzyjurss]

Is there a way to add a number at the end instead of the letter? I have pretty much the same situation but instead of having 4 numbers I need to make unique I have 4 letters. Thanks.

 

[bebo021999]

Try to give sample with input data and output result.

 

[izzyjurss]

This is a sample of my input and desired output