VBA: Add Suffix to Duplicate Value within Column

问题:

I hope you can help me resolving an issue in VBA with regard to adding a suffix to duplicate values. I have a range that looks like the following:

COL A:
000049
000050
000051
000052 (duplicate)
000052 (duplicate)
000053
000054

What I want to achieve is that once there is a duplicate in Column A, it adds a suffix to both numbers. I've only managed (using a loop) to set it for one of the fields.

Conditions:

1. If there is a duplicate, all duplicates must have a suffix;
2. If there is a new duplicate (let say we have 000052A and 000052B and we now move to 000061), the suffix count has to restart back from A.

Your help is really much appreciated!

回答1:

You can just do this with a formula:

=A1 & IF(COUNTIF(A:A,A1)>1, MID("ABCDEFGHIJK", COUNTIF($A$1:A1, A1), 1),"")

And copy down.

That says "Grab the value from A1 and if there is a duplicate anywhere in column A then go and get the character from string "ABCDEFGHIJK" that corresponds to how many times this value has appeared already in the list (from A1 to whatever row we are on)"