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

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

I appreciate any help on this one.

Thanks!!
 

Avit

Board Regular
Joined
Jan 9, 2013
Messages
56
Platform
MacOS
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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,706
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 9, 2013
Messages
56
Platform
MacOS
Sure, expected result here (let's assume for the first row only)

DateRole 1Role 2Role 3Role 4Role 5
MemberManagerOther
..
 

Avit

Board Regular
Joined
Jan 9, 2013
Messages
56
Platform
MacOS
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
 

Avit

Board Regular
Joined
Jan 9, 2013
Messages
56
Platform
MacOS
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.
 

Forum statistics

Threads
1,085,755
Messages
5,385,696
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top