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

[Avit]

Hi All,

Would really appreciate help on this one:
I have the following simplified table:

Date	Name 1	Name 2	Name 3	Name 4	Name 5
1/1/2020	John	Jack	Michael
1/2/2020	Jack	Tim	Jeff	Shawn
1/1/2020	Tim	Jeff
1/3/2020	David	John	Tim	Tom
1/2/2020	John	Michael
1/2/2020	Jim	Shawn
1/1/2020	David	David	Shawn
1/3/2020	Michael	Jack

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

Date	Name	Role
1/1/2020	Tim	Manager
1/1/2020	John	Member
1/1/2020	Jack	Manager
1/12020	Michael	Other
1/2/2020	John	Other
1/2/2020	Tom	Member
1/2/2020	Tim	Manager
1/3/2020	David	Member
1/3/2020	John	Member

.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:

And in this case of the example, the solution for the first role only is:

Date	Role 1	Role 2	Role 3	Role 4	Role 5
	Member	Manager	Other

..

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

Thanks!!

 

[Fluff]

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

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).