Find Value in Table and Return Column Header/Row Header for Each Occurrence

[jrussell19]

Hi all,

I'll simplify the table to help, but effectively I need to return the Column Header and Row Header of each occurrence of a specific value in a table. i.e.

Value: A

A B C D E F G
1 X X X X A X X
2 X X A X X X X
3 X X X X X X X
4 X X X X X X A
5 X X X X X X X
6 X X X A X X X
7 X X X X X A X

This needs to return:
Row Header Column Header
1 E
2 C
4 G
6 D
7 F

The initial table does not have a defined amount of rows. Additional rows will be added throughout the year.

I've been pulling my hair out with match,index,offset etc but can't for the life of me work it out! Any help would be absolutely amazing!

Thanks very much.

 

[Ankitsharmaece]

Hello Peter

No, still works for me. Here it is.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1		A	B	C	D	E	F	G		Value:	A	0
2	a	X	A	X	X	A	X	X				1
3	b	X	X	A	A	A	X	A				2
4	c	X	X	X	X	X	X	X
5	d	X	X	X	X	X	X	A				3
6	e	X	X	X	X	X	X	X
7	f	X	X	X	A	X	X	X				4
8	g	X	X	X	X	X	A	X				5
9
10	a	B	E
11	b	C	D	E	G
12	d	G
13	f	D
14	g	F
15
Headers Multiple (2)

1. Does the column L formula appear to be marking the correct rows?

2. Is the column A formula producing the correct letters in A10:A14?

3. If you still cannot resolve the problem, can you upload your sample file to a file-share site (eg Dropbox) and post a link here to that file?

4. What version of Excel & what operating system are you using?

Hello Peter ,
I copied your excel cells data exactly in my excel. after pasting it changes the result.
please help me.

skill matrix (Recovered).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		T1	T2	T3	T4	T5	T6	T7		Value:	PIC	0
2	Ankit	X	PIC	PIC	PIC	A	X	X				1
3	Ramesh	X	X	PIC	PIC	PIC	X	PIC				2
4	Minna	X	X	X	X	X	X	X
5	Abel	X	X	X	X	X	X	PIC				3
6	Kiley	X	X	X	X	PIC	X	X				4
7	Graciela	X	X	X	PIC	X	X	X				5
8	Ram	X	X	X	X	X	PIC	X				6
9
10	Ankit	T1
11	Ramesh	T1
12	Abel	T1
13	Kiley	T1
14	Graciela	T1
Sheet13

Cell Formulas
Range	Formula
L2:L8	L2	=IF(COUNTIF(B2:H2,K$1),LOOKUP(9.99E+307,L$1:L1)+1,"")
A10:A14	A10	=IF(ROWS(A$10:A10)>COUNT(L$2:L$8),"",LOOKUP(ROWS(A$10:A10),L$2:L$8,A$2:A$8))
B10:E14	B10	=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$8,MATCH($A10,$A$2:$A$8,0),0)=$K$1,COLUMN($B$1:$H$1)-COLUMN($B$1)+1),COLUMNS($B10:B10))),"")

 

[Peter_SSs]

I copied your excel cells data exactly in my excel. after pasting it changes the result.
please help me.

Welcome to the MrExcel board!

For the formula in B10, ...

Did you remember to confirm it with Ctrl+Shift+Enter, not just Enter before dragging it anywhere?

 

[sandy666]

maybe another way

Table1							Table2		Result
label	header 1	header 2	header 3	header 4	header 5		Search		label	header	Value
label 1	1	2	3	4	5		9		label 3	header 4	9
label 3	6	7	8	9	0				label 7	header 2	9
label 5	1	0	3	4	6
label 7	2	9	3	4	2

let
    Search = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"label"}, "header", "Value"),
    Join = Table.NestedJoin(Search,{"Search"},Unpivot,{"Value"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"label", "header", "Value"}, {"label", "header", "Value"}),
    RC = Table.RemoveColumns(Expand,{"Search"})
in
    RC

 

[Ankitsharmaece]

maybe another way

Table1							Table2		Result
label	header 1	header 2	header 3	header 4	header 5		Search		label	header	Value
label 1	1	2	3	4	5		9		label 3	header 4	9
label 3	6	7	8	9	0				label 7	header 2	9
label 5	1	0	3	4	6
label 7	2	9	3	4	2

let
    Search = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"label"}, "header", "Value"),
    Join = Table.NestedJoin(Search,{"Search"},Unpivot,{"Value"},"Table",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table", {"label", "header", "Value"}, {"label", "header", "Value"}),
    RC = Table.RemoveColumns(Expand,{"Search"})
in
    RC

How about multiple 9 in a single row.

 

[sandy666]

Table1							Table2		Result
label	header 1	header 2	header 3	header 4	header 5		Search		label	header	Value
label 1	1	2	3	4	5		9		label 3	header 4	9
label 3	6	7	8	9	0				label 5	header 2	9
label 5	1	9	9	9	6				label 5	header 3	9
label 7	2	9	3	4	2				label 5	header 4	9
									label 7	header 2	9

 

[Peter_SSs]

How about multiple 9 in a single row.

Did entering the earlier formula as an array formula (Ctrl+Shift+Enter) resolve the formula solution?

 

[Ankitsharmaece]

How about multiple 9 in a single row.

I haven't used power query before. I know excel formulas and little macros. But I haven't used this type of code in power query before. Can you guide me how to use this code. Thank you in advance.

 

[sandy666]

I am not a teacher so in short
your source table should be an Excel Table (Ctrl+T)
create second table as Excel Table as you can see in my post (Search)
then
Get Data - From Other Sources - Blank Query
open Advanced Editor and replace code there with copied from the post
make sure the appropriate tables and table names in the code are the same
Done
Close&Load
with my code you need to use the same headers
remember that Power Query is CASE SENSITIVE

for more information:

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

 

[Ankitsharmaece]

Thank you

I am not a teacher so in short
your source table should be an Excel Table (Ctrl+T)
create second table as Excel Table as you can see in my post (Search)
then
Get Data - From Other Sources - Blank Query
open Advanced Editor and replace code there with copied from the post
make sure the appropriate tables and table names in the code are the same
Done
Close&Load
with my code you need to use the same headers
remember that Power Query is CASE SENSITIVE

for more information:

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com

Thank you for your prompt response.

But what I need in header is diffrent types of training names~ 24 names. And Student names in Label ~66 . Will it work !! Or need to make any changes

 

[sandy666]

post your original table structure (headers) with generic data
and use XL2BB to do that