Join Team Hierarcy to SharePoint User list

[LLWest]

I've got 2 files (a team hierarchy from Remedy and a SharePoint user list of individual) that I need to join together in order to create a record in SharePoint for the SharePoint workflow to send an email notification. Each level of the hierarchy is a different field in SharePoint so that it executes the first workflow to the manager, and in 2 weeks to the Director and so on.   I'm unable to figure out how to join the Hierarchy with the user list so that I get the email for each individual but maintain the hierarchy structure.

The Remedy file has the team name along with manager, director and VP for each team.

File 1: Team Hierarchy (from Remedy)
Team	Manager Name	Director Name	VP Name
Team 1	Manager 1	Director 1	VP 1
Team 2	Manager 2	Director 2	VP 2
Team 3	Manager 3	Director 3	VP 3
Team 4	Manager 4	Director 4	VP 4
Team 5	Manager 5	Director 5	VP 5
Team 6	Manager 6	Director 6	VP 6
Team 7	Manager 7	Director 7	VP 7
Team 8	Manager 8	Director 8	VP 8

The User list of by each individual.

File 2: Email Addresses (from SharePoint)
Name	Email Address
Manager 1	Manager 1@company.com
Manager 2	Manager 2@company.com
Manager 3	Manager 3@company.com
Manager 4	Manager 4@company.com
Manager 5	Manager 5@company.com
Manager 6	Manager 6@company.com
Manager 7	Manager 7@company.com
Manager 8	Manager 8@company.com
Director 1	Director 1@company.com
Director 2	Director 2@company.com
Director 3	Director 3@company.com
Director 4	Director 4@company.com
Director 5	Director 5@company.com
Director 6	Director 6@company.com
Director 7	Director 7@company.com
Director 8	Director 8@company.com
VP 1	VP 1@company.com
VP 2	VP 2@company.com
VP 3	VP 3@company.com
VP 4	VP 4@company.com
VP 5	VP 5@company.com
VP 6	VP 6@company.com
VP 7	VP 7@company.com
VP 8	VP 8@company.com

Preferred output would look like:

File 3: Team with hierarchy and email addresses
Team	Manager	Manager Email Address	Director	Director Email	VP	VP Email
Team 1	Manager 1	Manager 1@company.com	Director 1	Director 1@company.com	VP 1	VP 1@company.com
Team 2	Manager 2	Manager 2@company.com	Director 2	Director 2@company.com	VP 2	VP 2@company.com
Team 3	Manager 3	Manager 3@company.com	Director 3	Director 3@company.com	VP 3	VP 3@company.com
Team 4	Manager 4	Manager 4@company.com	Director 4	Director 4@company.com	VP 4	VP 4@company.com
Team 5	Manager 5	Manager 5@company.com	Director 5	Director 5@company.com	VP 5	VP 5@company.com
Team 6	Manager 6	Manager 6@company.com	Director 6	Director 6@company.com	VP 6	VP 6@company.com
Team 7	Manager 7	Manager 7@company.com	Director 7	Director 7@company.com	VP 7	VP 7@company.com
Team 8	Manager 8	Manager 8@company.com	Director 8	Director 8@company.com	VP 8	VP 8@company.com

I tried to join on the name, but it only joins the one field and not the hierarchy.  I know it's got to be easy, but it's got me stumped.  any insight or help is appreciated.  thanks!

[NicoleJohnson · Accepted solution]

Try using the Transpose tool while grouping by Team Name (to keep things grouped together by team), which will put all your Manager/Director/VP names in one column as values... then join that Value column to the list of names + emails using a Join or Find & Replace tool. You can then Cross-Tab the results back to get to your original layout (with a little tweaking...) - I included a workflow that shows a few tricks using Formula and Multi-Field Formula tools to get the data parsed in the right fields... hope that helps!

Cheers,

NJ

[LLWest]

Thanks so much!   this worked perfect and I learned about the Multi-Field Formula that I would have never thought to use before.