VBA:将后缀添加到列中的重复值 [英] VBA: Add Suffix to Duplicate Value within Column

问题描述

希望您能帮助我解决VBA中关于为重复值添加后缀的问题.我的范围如下所示:

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

我想要实现的是，一旦A列中有重复项，它将为两个数字添加一个后缀.我只设法(使用循环)将其设置为其中一个字段.

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.

条件:

1. 如果有重复项，则所有重复项都必须带有后缀；
2. 如果有新的重复项(假设我们有000052A和000052B，现在移至000061)，则后缀计数必须从A重新开始.

非常感谢您的帮助！

推荐答案

您可以使用以下公式进行操作:

You can just do this with a formula:

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

然后抄下来.

这表示从A1中获取值，并且如果A列中的任何地方都有重复项，则从字符串"ABCDEFGHIJK"中获取字符，该字符对应于该值已出现在列表中的次数(从A1到无论我们在哪一行)"

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

这篇关于VBA:将后缀添加到列中的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！