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

Popular posts from this blog

How has firefox/gecko HTML+CSS rendering changed in version 38? -

javascript - Complex json ng-repeat -

jquery - Cloning of rows and columns from the old table into the new with colSpan and rowSpan -