Forum Discussion

imsureshbio1109's avatar
imsureshbio1109
Copper Contributor
Sep 27, 2022
Solved

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 an...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Sep 27, 2022

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 27, 2022

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.

Resources