Custom column that returns main eMail adress based on similar values

[PuddleRunna]

Hi all,

Coudln't even come up with a proper subject for this 🙈 I've been stuck on this for ages.

I have a table "Employees" looking like this:

First Name	Name	eMail	Department
Jane	Doe	jane.doe@company.com	Xyz
Jane	Doe_sec	jane.doe_sec@company.com	Sec
John	Miller	john.miller1@company.com	Zyx
John	Miller_sec	john.miller_sec@company.com	Sec
Jack	Jones	jack.jones01@company.com	Abc
Jack	Jones_sec	jack.jones_sec@company.com	Sec
Melvin	Doe	melvin.doe@company.com	Edf
Kevin	Doe	Kevin.doe@company.com	Ghi

Some employees have a secondary profile marked by *_sec*. I have reports on the _sec users (using site filter "contains: *_sec@company.com"). What I need is to be able to get the _sec users main eMail and department, i.e.

Jack Jones_sec maps to jack.jones01@company.com and Department Abc.

The difficulty here is that main adresses could include numbers at the end, so simply removing the _sec part doesn't do the trick.

I've tried duplicating the table and create a custom column for "Name" that removes the _sec part. Then mapped "Name" to 'Employees[Name] but that doesn't really work as there's several Doe's etc.

Appreciate any hints and tips 🙂

[Anonymous]

For accuracy you would really need some type of unique identifier that is shared amount the main and seconday profile. The example in your post appears to show that there is no way to when a user's main email address has numbers at the end.

[PuddleRunna]

Yeah, exactly my problem. It's a 60k records database list though, so my chances of getting that indicator are slim to none 😞

[amitchandak]

@PuddleRunna , Try new column like

new Department =
var _email = SUBSTITUTE([eMail], "_sec", "") // or use SUBSTITUTE([eMail], "_sec@", "@")
return
if(search("_sec", [eMail],,0) >0 , maxx(filter(Table, [eMail] =_email),[Department]),[Department])

[PuddleRunna]

Thanks a lot, Amit! This at least solves my problem for eMails that do not have numbers, which is the majority in this special case 🙂

Is there any way to make it look for eMail adresses that contain the substituted string and return the results?