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

I appreciate any help on this one.

Thanks!!

 

[sandy666]

something like this?

 

[Avit]

Thanks. Close but not quite. I need the content but not the header, because there are actually hundreds of names. and they need to be in the same order as they appear on the first table (so in the first row, it will be filled for the first 3 columns, and for the second row, for the first 4 columns.
Does that make sense?

 

[sandy666]

Does that make sense?

no, until you show expected result based on your example

 

[Peter_SSs]

create an additional table of the same size as the first (9x6), but instead of names, I will see the roles they play.

If what sandy666 has posted is not the desired result, please provide the full expected 9x6 result table with any further clarification about how the values in it are obtained manually

Edit: Oops, the last 2 posts made while I was procrastinating about mine.

 

[Avit]

Sure, expected result here (let's assume for the first row only)

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

..

 

[Avit]

Let me clarify, there is a finite number of roles for each row (usually 2-3, but could go up to 8 or 9 in rare occasions), but there are hundreds of names, which is why I don't want those hundreds of names to create a new column, but just to have the maximum of 8-9 (and usually 2-3) columns

 

[sandy666]

???

 

[Avit]

Yes, something like that.
I am sorry that my example is not great (which is why I only solved for the first row,), but yes indeed - something like that would be perfect.

 

[sandy666]

You can do that with Power Query, are you interested?