phillipus2005
New Member
- Joined
- Jun 29, 2018
- Messages
- 34
- Office Version
- 365
- Platform
- Windows
- MacOS
- 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
Table 2
Ideal output
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
Case | Data1 | Data2 | Names |
Case 1 | XXX | XXX | Person A, Person B, Person C |
Case 2 | XXX | XXX | Person B, Person D |
Case 3 | XXX | XXX | Person D, Person A, Person B |
Table 2
Names |
Person A |
Person B |
Person C |
Person D |
Ideal output
Person | Cases |
Person A | Case 1 |
Case 3 | |
Person B | Case 1 |
Case 2 | |
Case 3 | |
Person C | Case 1 |
Person D | Case 2 |
Case 3 |