Lookup/Pivot from multiple tables

phillipus2005

New Member
Joined
Jun 29, 2018
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
Hi
I have two tables in excel, one has records of cases (one per row), with a column showing team members involved (multiple names in one cell, separated by commas). the second table has an extract of all those names (one name per cell). I would like to use a pivot table to show which cases each person has been involved with... (see below for a simplified summary of the data and ideal output)

I've tried using power queries but the closest i've come is getting a pivot table with every team member listed under every case...

Thanks in advance

Here's a simplified summary of the data

Table 1
CaseData1Data2Names
Case 1XXXXXXPerson A, Person B, Person C
Case 2XXXXXXPerson B, Person D
Case 3XXXXXXPerson D, Person A, Person B

Table 2
Names
Person A
Person B
Person C
Person D

Ideal output
PersonCases
Person ACase 1
Case 3
Person BCase 1
Case 2
Case 3
Person CCase 1
Person DCase 2
Case 3
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You can do that fairly easily in power query. Hightlight you data table. Under the data tab, select from table or range. Once in power query you highlight your names column. From the home tab, you 'split column', change to comma then in same window under advanced you change to rows. Finally in transform tab you 'format' and trim.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top