在列中使用随机字母重命名重复项 - Excel [英] Rename duplicates with random alphabets in a column - Excel
问题描述
我在 A 列中有一个包含 500 个名字的列表.
I have a list of 500 names in column A.
1 name1
2 name2
3 name3
4 name1
5 name2
6 name3
7 name1
8 name2
9 name3
..
而且我需要为重复项添加前缀或后缀字母.我应该得到
And i need to add a prefix or suffix alphabets to the duplicates. And I should get
1 name1
2 a.name1
3 b.name1
4 name2
5 a.name2
6 b.name2
7 name3
8 a.name3
9 b.name3
..
我选择了重复的值
select column -> conditional formatting -> Highlight Cell Rules -> Duplicate Values
如何使用随机字母前缀或后缀重命名重复值
How to rename duplicate values with random alphabets prefix or suffix
推荐答案
如果你更喜欢使用只包含下一个字母的非随机前缀,你可以从 C2 开始做这样的事情:-
If you prefer to use a non-random prefix that just contains the next letter, you could do something like this starting in C2:-
=IF(B2=B1,CHAR(96+COUNTIF(B$1:B1,B2))&"."&B1,B2)
我假设名称已排序、有标题并位于 B 列中.
I'm assuming that the names are sorted, have a heading and are in column B.
如果你不想对它们进行排序,这仍然可以修改:-
If you didn't want to sort them, this would still work with a modification:-
=IF(COUNTIF(A$1:A1,A2)>0,CHAR(96+COUNTIF(A$1:A1,A2))&"."&A2,A2)
我假设未排序的名称在 A 列中,并带有标题.
I'm assuming that the unsorted names are in column A, with a header.
这篇关于在列中使用随机字母重命名重复项 - Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!