Rename duplicates with random alphabets in a column - Excel -
i have 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 need add prefix or suffix alphabets duplicates. , should
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 have selected duplicate values
select column -> conditional formatting -> highlight cell rules -> duplicate values
how rename duplicate values random alphabets prefix or suffix
if prefer use non-random prefix contains next letter, starting in c2:-
=if(b2=b1,char(96+countif(b$1:b1,b2))&"."&b1,b2)
i'm assuming names sorted, have heading , in column b.
if didn't want sort them, still work modification:-
=if(countif(a$1:a1,a2)>0,char(96+countif(a$1:a1,a2))&"."&a2,a2)
i'm assuming unsorted names in column a, header.
Comments
Post a Comment