matching two data sets, of different sizes where one is horizontal

Status
Not open for further replies.

Avit

Board Regular
Joined
Jan 9, 2013
Messages
56
Platform
MacOS
Hi All,

Would really appreciate help on this one:
I have the following simplified table:
DateName 1Name 2Name 3Name 4Name 5
1/1/2020JohnJackMichael
1/2/2020JackTimJeffShawn
1/1/2020TimJeff
1/3/2020DavidJohnTimTom
1/2/2020JohnMichael
1/2/2020JimShawn
1/1/2020DavidDavidShawn
1/3/2020MichaelJack

I need to match each one of these people to their role on their team (let's say, "Manager", "Member" and "Other" based on a second vertical dataset. Let's also assume, that each of the people can be in any of the 3 roles for any given "shift". (but just on one role for that shift).
so, for example
DateNameRole
1/1/2020TimManager
1/1/2020JohnMember
1/1/2020JackManager
1/12020MichaelOther
1/2/2020JohnOther
1/2/2020TomMember
1/2/2020TimManager
1/3/2020DavidMember
1/3/2020JohnMember
.Let's also assume that each of the "jobs" (on the first table), might or might not have a manger (usually if there is no manager, than the only name that appears is an "other":
Please also note that the tables are not exactly equal (there is some data missing in the second table, that exists in the first).

I want to match the second table with the first one, so that I can create an additional table of the same size as the first (9x6), but instead of names, I will see the roles they play. so for example in the first row instead of the names John, Jack and Michael, I will see Member, Manager, Other

since there are hundreds of "names", but only a finite number of roles (usually 2-3, and no more than 8-9), the ideal solution will show something like this:
1580887462344.png


And in this case of the example, the solution for the first role only is:
DateRole 1Role 2Role 3Role 4Role 5
MemberManagerOther
..

I appreciate any help on this one.
(if it matters, I am using google sheets, due to company restrictions).

Thanks!!
 
Status
Not open for further replies.

Forum statistics

Threads
1,085,183
Messages
5,382,172
Members
401,779
Latest member
Thonor

Some videos you may like

This Week's Hot Topics

Top