How to split Users Names so that I can look up their email

Help Studio
, ,
1

I have two excel sheets. The first is a job that has project number and project manager. The interesting part is that the project manager field can have multiple project managers like below:
Project Number Project Manager
123 John Doe
1234 John Doe, Jane Doe

Then I have a second sheet that has Project Manager and email like below:
Project Manager Email
John Doe JohnDoe@fakeemail.com
Jane Doe JaneDoe@fakeemail.com

My question is I am currently looking up the project manager in the first sheet, finding that name on the second sheet and grabbing the email. Now I run into the issue when there are multiple project managers. How do I adjust this in my process?

2

@Kocourek_Kyle,

I would say not a good approach to find email id on the basis of name. There is high chance that same name people there and this logic will fail.

There should be some unique value to lookup which will be reliable like employee id.

Do check feasibility about the unique id which will make your bot error proof and reliable.

Thanks,
Ashok :slightly_smiling_face:

3

Yeah that part doesn’t really matter. Im just using a fake scenario. What I really want to know is how to solve the Multiple Project Managers in one column. How do I look up both of their emails.

Even if it were employee number instead of project manager names. The issue is still there.

4

Well tell us how it should be handled. That’s part of analyzing a process. Should it throw an exception? Always choose the first one? Take both emails and put them in a list? It’s entirely up to you and your process.

5

Yes I’d like it to still grab both emails so that I can then later send an outlook mail message to those email addresses.

6

Please show screenshots of how the two Excel sheets are laid out.

8

1/ take “project_manager_field” from first sheet
2/ split it into individual managers - it will give you array of “managers”
array_of_managers = split(project_manager_field, ",")
3/ for each manager in the array_of_managers find the name in second sheet

It assumes:

  • separator is “,” (comma)
  • manager’s name doesn’t contain “,”

Cheers