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

 

[Fluff]

Hi & welcome to MrExcel.
How about

Book1
	A	B	C	D	E
1	USA	Spain	China	Canada	Mexico
2	Janitor	Payroll	Janitor	HR	Manager
3		Operator	Payroll	Janitor
4		Manager2	DVP
5			HR
6
7
8	Janitor	USA
9		China
10		Canada
Report

Cell Formulas
Range	Formula
B8	=IFERROR(INDEX($A$1:$E$1,AGGREGATE(15,6,(COLUMN($A$1:$E$1)-COLUMN($A$1)+1)/($A$2:$E$5=$A$8),ROWS($A$1:$A1))),"")

 

[Rosa94]

You're AMAZING!!!!
can you please explain each piece of the formula for me? My table is much larger and am not sure how the "(15,6," comes into play.
THANK YOU SOOOO MUCH!!!

 

[Fluff]

Have a look here & see if it helps. https://exceljet.net/formula/extract-all-partial-matches
All you need to change is the
$A$1:$E$1
to ensure it covers the range of your headers, and
$A$2:$E$5
to cover the range of data (excludig the header)

 

[Rosa94]

Thank you soooo much!!!

Do you have a suggestion of maybe something better to use if i'm doing this on a LARGE scale? Ex a table of 16,000 cells of data and 130 jobs to look for with possibly 100 countries.

 

[Fluff]

If the formula is too slow, then you could use either a database, or VBA.

 

[Fluff]

It may also be possible to do this with Power Query/Get & Transform

 

[sandy666]

It may also be possible to do this with Power Query/Get & Transform

Thanks Fluff

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]USA[/td][td=bgcolor:#5B9BD5]Spain[/td][td=bgcolor:#5B9BD5]China[/td][td=bgcolor:#5B9BD5]Canada[/td][td=bgcolor:#5B9BD5]Mexico[/td][td][/td][td=bgcolor:#DDEBF7]Name[/td][td=bgcolor:#DDEBF7]Janitor[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Janitor[/td][td=bgcolor:#DDEBF7]Payroll[/td][td=bgcolor:#DDEBF7]Janitor[/td][td=bgcolor:#DDEBF7]HR[/td][td=bgcolor:#DDEBF7]Manager[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Operator[/td][td]Payroll[/td][td]Janitor[/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Header[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Manager2[/td][td=bgcolor:#DDEBF7]DVP[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td]Canada[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]HR[/td][td][/td][td][/td][td][/td][td]China[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]USA[/td][td][/td][/tr]
[/table]

With PowerQuery and PivotTable

[SIZE=1]// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    Transpose = Table.Transpose(Demote),
    Unpivot = Table.UnpivotOtherColumns(Transpose, {"Column1"}, "Attribute", "Value"),
    ROC = Table.SelectColumns(Unpivot,{"Column1", "Value"}),
    Rename = Table.RenameColumns(ROC,{{"Column1", "Header"}, {"Value", "Name"}})
in
    Rename[/SIZE]

 

[Rosa94]

Oh man! I'm excited and scared at the same time.

I am not familiar with PowerQuery and have not used PivotTables very often. Is there a a link you can send me to help me figure out your beautiful work?

Have I told you how amazing your are?

 

[sandy666]

but solution need PowerQuery

you can download it here: Example file

Excel 2010 / 2013 require PowerQuery add-in (from MS site for free)
Higher version has PowerQuery (aka Get&Transform) built-in