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?


[TABLE="width: 787"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Program[/TD]
[TD]Person A[/TD]
[TD]Person B[/TD]
[TD]Person C[/TD]
[TD]Person D[/TD]
[/TR]
[TR]
[TD]Program CNBC[/TD]
[TD]Angela[/TD]
[TD] Geoff[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Program CNN[/TD]
[TD]Ashley[/TD]
[TD] Shelby[/TD]
[TD] Geoff[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Program ZTV[/TD]
[TD]Ashley[/TD]
[TD] Shelby[/TD]
[TD] Angela[/TD]
[TD] Chris[/TD]
[/TR]
[TR]
[TD]Program ESPN[/TD]
[TD]Geoff[/TD]
[TD] James[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Program Fox[/TD]
[TD]Ashley[/TD]
[TD] Shelby[/TD]
[TD] Chris[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

something ike this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Value[/td][td=bgcolor:#70AD47]Program[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Angela[/td][td=bgcolor:#E2EFDA]Program CNBC, Program ZTV[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Geoff[/td][td]Program CNBC, Program CNN, Program ESPN[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ashley[/td][td=bgcolor:#E2EFDA]Program CNN, Program ZTV, Program Fox[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Shelby[/td][td]Program CNN, Program ZTV, Program Fox[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Chris[/td][td=bgcolor:#E2EFDA]Program ZTV, Program Fox[/td][/tr]

[tr=bgcolor:#FFFFFF][td]James[/td][td]Program ESPN[/td][/tr]
[/table]

 

[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