Help request to create a unique id by macro or formula

[mso3]

Hi friends,

I want to give a unique ID to each person.
It should be given alphabetically.
Column ‘D’ contains surname.
Column ‘H’ contains ID.
If the surname starts with a letter ‘A’ then a ID should start with a letter ‘A0001’
Second time If the surname starts with a letter ‘A’ then a ID should start with a letter ‘A0002’ and so on …


If the surname starts with a letter ‘B’ then a ID should start with a letter ‘B0001’
Second time If the surname starts with a letter ‘B’ then a ID should start with a letter ‘B0002’ and so on …


It should be same for each letter from A to Z respectively.

Please suggest me solution macro or formula.

Thanking you in anticipation.

[shukla.ankur281190]

Try
H8=LEFT(D8,1)&"000"&SUM(1*((LEFT($D$8:D8,1)=LEFT(D8,1)))) with CTRL+SHIFT+ENTER and drag down

Please Login or Register  to view this content.

[JohnTopley]

Try ..

Please Login or Register  to view this content.

[bebo021999]

Try:

=LEFT(D8,1)&TEXT(SUMPRODUCT(--(CODE($D$8:D8)=CODE(D8))),"0000")
Drag down

[mso3]

Hi all,

Perfect! All the formulas are working fine.

I appreciate you for the same.

Thank you and have a nice time.