Create unique email address

[imsureshbio1109]

Hi all, 

I want to create unique email addresses for all the new employees in my firm. I will get the first name and last name from the HR Team. I have the master database for all the previous and current employees. 

So If two employees with the same name join, I have to add 1 to the last name and so on...up to 10.

Can I use Vlookup and write a condition to look for the existing email address and write a new one... 

Support me if you have already solved the above challenge

[#2 · Accepted solution]

@imsureshbio1109 Without making it overly complicated I would introduce two helper columns. One that joins the the First and Last names and one that counts the occurrences of these names in the rows above. Have a look at the attached file for an example.

[#3]

@imsureshbio1109 

Firstly, you will need a uniquely assigned staff number.  Otherwise, you will not know which way round to assign the email addresses even when you have them.  VLOOKUP is not good enough because it will only detect a single instance of a matching name.

=  LET(
   base,     first & "." & last,
   number,   COUNTIFS(first,first, last,last, staffNum,"<"&staffNum),
   modifier, IF(number, number, ""),
   email,    base & modifier & "@" & domain,
   email)

This is for 365; legacy solutions will look very different and often require an understanding of mixed referencing.

[#4]

@Riny_van_Eekelen Thanks Riny. This will do the trick