Forum Discussion
Create unique email address
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
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 Replies
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.
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.
Riny_van_Eekelen Thanks Riny. This will do the trick
