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

Status
Not open for further replies.

#### Avit

##### Board Regular
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

##### MrExcel MVP, Moderator
Status
Not open for further replies.

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