在列中重命名带有随机字母的重复项 - Excel [英] Rename duplicates with random alphabets in a column - Excel
问题描述
1 name1
2 name2
3 name3
4 name1
5 name2
6 name3
7 name1
8 name2
9 name3
..
我需要为重复项添加前缀或后缀字母。我应该得到
1 name1
2 a.name1
3 b.name1
4 name2
5 a.name2
6 b.name2
7 name3
8 a.name3
9 b.name3
..
我已选择重复值
code>选择列 - >条件格式 - >突出显示细胞规则 - >重复值
如何使用随机字母前缀或后缀重命名重复值
如果您喜欢使用只包含下一个字母的非随机前缀,则可以从C2开始执行类似的操作: -
= IF(B2 = B1,CHAR(96 + COUNTIF(B $ 1:B1,B2))&& B1,B2 )
我假定这些名称是排序的,有一个标题,在列B中。 / p>
如果您不想对它们进行排序,这仍然可以进行修改: -
= IF(COUNTIF(A $ 1:A1,A2)> 0,CHAR(96 + COUNTIF(A $ 1:A1,A2))& 。&A2,A2)
我假设未排序的名称在列A ,标题。
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
..
And i have selected duplicate values
select column -> conditional formatting -> Highlight Cell Rules -> Duplicate Values
How to rename duplicate values with random alphabets prefix or suffix
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)
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)
I'm assuming that the unsorted names are in column A, with a header.
这篇关于在列中重命名带有随机字母的重复项 - Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!