Join Team Hierarcy to SharePoint User list

Question

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) TeamManager NameDirector NameVP NameTeam 1Manager 1Director 1VP 1Team 2Manager 2Director 2VP 2Team 3Manager 3Director 3VP 3Team 4Manager 4Director 4VP 4Team 5Manager 5Director 5VP 5Team 6Manager 6Director 6VP 6Team 7Manager 7Director 7VP 7Team 8Manager 8Director 8VP 8

The User list of by each individual.

File 2: Email Addresses (from SharePoint)NameEmail AddressManager 1Manager 1@company.comManager 2Manager 2@company.comManager 3Manager 3@company.comManager 4Manager 4@company.comManager 5Manager 5@company.comManager 6Manager 6@company.comManager 7Manager 7@company.comManager 8Manager 8@company.comDirector 1Director 1@company.comDirector 2Director 2@company.comDirector 3Director 3@company.comDirector 4Director 4@company.comDirector 5Director 5@company.comDirector 6Director 6@company.comDirector 7Director 7@company.comDirector 8Director 8@company.comVP 1VP 1@company.comVP 2VP 2@company.comVP 3VP 3@company.comVP 4VP 4@company.comVP 5VP 5@company.comVP 6VP 6@company.comVP 7VP 7@company.comVP 8VP 8@company.com

Preferred output would look like:

File 3: Team with hierarchy and email addresses    TeamManagerManager Email AddressDirectorDirector EmailVPVP Email Team 1Manager 1Manager 1@company.comDirector 1Director 1@company.comVP 1VP 1@company.comTeam 2Manager 2Manager 2@company.comDirector 2Director 2@company.comVP 2VP 2@company.comTeam 3Manager 3Manager 3@company.comDirector 3Director 3@company.comVP 3VP 3@company.comTeam 4Manager 4Manager 4@company.comDirector 4Director 4@company.comVP 4VP 4@company.comTeam 5Manager 5Manager 5@company.comDirector 5Director 5@company.comVP 5VP 5@company.comTeam 6Manager 6Manager 6@company.comDirector 6Director 6@company.comVP 6VP 6@company.comTeam 7Manager 7Manager 7@company.comDirector 7Director 7@company.comVP 7VP 7@company.comTeam 8Manager 8Manager 8@company.comDirector 8Director 8@company.comVP 8VP 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 Answer

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