people assigned to a project - how to know who to go to for each?

[djveed]

Hi everyone! I have a list of programs and for each of them, a list of names that worked on the program, anywhere between one and four people. If I want to synthesize this data and know, for example, what programs should i ask "Geoff" about, how do I do this in Excel?

Program	Person A	Person B	Person C	Person D
Program CNBC	Angela	Geoff
Program CNN	Ashley	Shelby	Geoff
Program ZTV	Ashley	Shelby	Angela	Chris
Program ESPN	Geoff	James
Program Fox	Ashley	Shelby	Chris

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[sandy666]

something ike this?

Value	Program
Angela	Program CNBC, Program ZTV
Geoff	Program CNBC, Program CNN, Program ESPN
Ashley	Program CNN, Program ZTV, Program Fox
Shelby	Program CNN, Program ZTV, Program Fox
Chris	Program ZTV, Program Fox
James	Program ESPN

 

[djveed]

Yes! Something like that. How do I do that in Excel? Obviously I have far more than the six names listed in my data file.

 

[sandy666]

are you able to use PowerQuery (Get&Transform) ?

 

[djveed]

are you able to use PowerQuery (Get&Transform) ?

Actually I'm not totally sure. I'm pretty sure I have access to that but I've never used it.

 

[sandy666]

Excel 2010/2013 - PowerQuery add-in
Excel 2016 and higher - built-in aka Get&Transform

 

[djveed]

Okay. I may not actually have access to that add-in. Can you help me with this? My data file is only 20 Projects long.

 

[sandy666]

you can download PowerQuery add-in from MS site, it's free
if not - I can't help

if you want use PQ you must have this add-in, if not you must wait for someone else with another solution

 

[djveed]

Thank you! Let me try this. If this doesn't work - is there any way to do this without the add-in? Anyone know?

 

[sandy666]

you are welcome

and yes, upgrade your office to minimum 2016