return column header for each value occurrence in a table

[Rosa94]

Hello,

I have a table countries and jobs in that country. I am looking to select the job and get all the countries that job is in. Example: select Janitor and the output will show USA,China and Canada

[TABLE="class: grid, width: 10"]
<tbody>[TR]
[TD]USA[/TD]
[TD]Spain[/TD]
[TD]China[/TD]
[TD]Canada[/TD]
[TD]Mexico[/TD]
[/TR]
[TR]
[TD]Janitor[/TD]
[TD]Payroll[/TD]
[TD]Janitor[/TD]
[TD]HR[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Operator[/TD]
[TD]Payroll[/TD]
[TD]Janitor[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Manager2[/TD]
[TD]DVP[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]HR[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Is this something excel can do or is this something only access can process?
Thank you for your help.
Rosa

 

[sandy666]

Have I told you how amazing your are?

I know, but I have not won the Nobel Prize yet

 

[Rosa94]

Where do i put the lovely pivot table code you provided me?

How do i get the Header and Name options on my table? When i try to create my pivot table I get the list of countries for my options.

 

[sandy666]

Pivot Table is created from Query not from your source table directly

see here: Movie

 

[Rosa94]

i cant get my power query to work . I'm still getting the list of countries to choose from not "header" and "name"

 

[sandy666]

ab ovo...

1. copy M-code from the post
2. select your source range
3. from the ribbon -Data - From Table (it will open new window)
4. in Advanced Editor replace code with copied code (make sure the name of the table is the same like in replaced code)
5. it should show transformed table in PQ Editor
6. Close&Load as .... Connection only

then
Insert - Pivot Table ... the rest is on the movie

 

[Rosa94]

you guys are GENIUS!!!! A million thank you's for all your help!!

 

[sandy666]

You are welcome

Now you can click Like/Thanks at the bottom left corner in post(s) which helped you

Have a nice day