return row and column headers for matrix for duplicate values

[DeonRobbie]

Hi all
I am busting my head for past few days in getting the formulas to work. I have a building condition survey spreadsheet where the physical condition of different items in different rooms get evaluated. Each item in each room receive a value from 0 to 3. 0=good and 3 = severe / critical.
The matrix is 17 rows by 12 columns, with items inspected in the first column, and rooms inspected in the first row.I need a method to find all cells with condition 3 to return the corresponding column header and row. Any possible suggestions?

Regards DeonRobbie

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Room 1	Room 2	Room 3	Room 4	Room 5	Room 6	Room 7	Room 8	Room 9	Room 10	Room 11	Room 12
2	A															B	Room 2
3	B			3			3									B	Room 5
4	C															D	Room 5
5	D						3									R	Room 1
6	E															R	Room 12
7	F
8	G
9	H
10	I
11	J
12	K
13	L
14	M
15	N
16	O
17	P
18	Q
19	R		3											3
Master

Cell Formulas
Range	Formula
P2:P7	P2	=IFERROR(INDEX($A$2:$A$19,AGGREGATE(15,6,(ROW($A$2:$A$19)-ROW($A$2)+1)/($C$2:$N$19=3),ROWS(P$2:P2))),"")
Q2:Q7	Q2	=IFERROR(INDEX($C$1:$N$1,AGGREGATE(15,6,(COLUMN($C$1:$N$1)-COLUMN($C$1)+1)/($A$2:$A$19=P2)/($C$2:$N$19=3),COUNTIFS(P$2:P2,P2))),"")

 

[Toadstool]

Looks like @Fluff and I came up with the same approach...

DeonRobbie.xlsx
	A	B	C	D	E	F	G	H	I
1		Outside	Bedroom 1	Bedroom 2	Bedroom 3	Bathroom	Toilet	Lounge	Kitchen
2	Structure	1	1	1	1	1	1	1	1
3	Roof	1	3	1	1	1	1	1	0
4	Walls	1	1	3	0	1	1	0	0
5	Floors	1	1	1	3	3	3	3	1
6	Ceilings			1	1	1	1	1	3
7	Stairs	1	1		1	1	3	0	0
8	Windows	1	1			1	1	1	1
9	Doors	1	1	1	1	1		1	1
10
11	Severe and Critical Defects (only items marked "3" above).
12	Roof	Bedroom 1
13	Walls	Bedroom 2
14	Floors	Bedroom 3
15	Floors	Bathroom
16	Floors	Toilet
17	Floors	Lounge
18	Ceilings	Kitchen
19	Stairs	Toilet
20
Sheet1

Cell Formulas
Range	Formula
A12:A20	A12	=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,ROW($B$2:$I$9)-ROW($A$1)/($B$2:$I$9=3),ROW()-ROW($A$11))),"")
B12:B20	B12	=IFERROR(INDEX($B$1:$I$1,AGGREGATE(15,6,COLUMN($B$1:$I$1)-COLUMN($A$1)/(($A$2:$A$9=$A12)*($B$2:$I$9=3)),COUNTIF($A$12:$A12,$A12))),"")

 

[DeonRobbie]

Thank you Fluff and Toadstool, works like a charm. Much appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[PrettyGood_Not Great]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Room 1	Room 2	Room 3	Room 4	Room 5	Room 6	Room 7	Room 8	Room 9	Room 10	Room 11	Room 12
2	A															B	Room 2
3	B			3			3									B	Room 5
4	C															D	Room 5
5	D						3									R	Room 1
6	E															R	Room 12
7	F
8	G
9	H
10	I
11	J
12	K
13	L
14	M
15	N
16	O
17	P
18	Q
19	R		3											3
Master

Cell Formulas
Range	Formula
P2:P7	P2	=IFERROR(INDEX($A$2:$A$19,AGGREGATE(15,6,(ROW($A$2:$A$19)-ROW($A$2)+1)/($C$2:$N$19=3),ROWS(P$2:P2))),"")
Q2:Q7	Q2	=IFERROR(INDEX($C$1:$N$1,AGGREGATE(15,6,(COLUMN($C$1:$N$1)-COLUMN($C$1)+1)/($A$2:$A$19=P2)/($C$2:$N$19=3),COUNTIFS(P$2:P2,P2))),"")

Is there a way to do exactly this but return cells with any value not equal to zero? I am trying to get the Row and Column Header into one dynamic list for each non zero cell in the array.

 

[Peter_SSs]

Is there a way to do exactly this but return cells with any value not equal to zero?

Welcome to the MrExcel board!
Is this what you mean?

23 11 25.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1			Room 1	Room 2	Room 3	Room 4	Room 5	Room 6	Room 7	Room 8	Room 9	Room 10	Room 11	Room 12
2	A															B	Room 2
3	B			3			3		0	0	0					B	Room 5
4	C								0	0	0					D	Room 5
5	D						3		0	0	0					E	Room 1
6	E		5						0	0	0					G	Room 10
7	F								0	0	0					L	Room 9
8	G								0	0	0	22				R	Room 1
9	H								0	0	0					R	Room 5
10	I				0				0	0	0					R	Room 10
11	J								0	0	0					R	Room 11
12	K								0	0	0					R	Room 12
13	L										33
14	M
15	N
16	O
17	P
18	Q
19	R		3				2		0	0	0	9	8	3
Row Col Headers

Cell Formulas
Range	Formula
P2:P19	P2	=IFERROR(INDEX($A$2:$A$19,AGGREGATE(15,6,(ROW($A$2:$A$19)-ROW($A$2)+1)/($C$2:$N$19<>0),ROWS(P$2:P2))),"")
Q2:Q19	Q2	=IFERROR(INDEX($C$1:$N$1,AGGREGATE(15,6,(COLUMN($C$1:$N$1)-COLUMN($C$1)+1)/($A$2:$A$19=P2)/($C$2:$N$19<>0),COUNTIFS(P$2:P2,P2))),"")

 

[PrettyGood_Not Great]

Hi Peter, thanks for the quick reply. I have tried <>O but this returns the column and row header corresponding to A1 in my matrix, for all results, and A1 is blank.

 

[Peter_SSs]

and A1 is blank.

.. but perhaps contains a formula returning "" so is not in fact blank?
Try these instead
P2 copied down:

=IFERROR(INDEX(A$2:A$19,AGGREGATE(15,6,(ROW(A$2:A$19)-ROW(A$2)+1)/(C$2:N$19<>"")/(C$2:N$19<>0),ROWS(P$2:P2))),"")

Q2 copied down:

=IFERROR(INDEX(C$1:N$1,AGGREGATE(15,6,(COLUMN(C$1:N$1)-COLUMN(C$1)+1)/(A$2:A$19=P2)/(C$2:N$19<>"")/(C$2:N$19<>0),COUNTIFS(P$2:P2,P2))),"")

 

[PrettyGood_Not Great]

.. but perhaps contains a formula returning "" so is not in fact blank?
Try these instead
P2 copied down:

=IFERROR(INDEX(A$2:A$19,AGGREGATE(15,6,(ROW(A$2:A$19)-ROW(A$2)+1)/(C$2:N$19<>"")/(C$2:N$19<>0),ROWS(P$2:P2))),"")

Q2 copied down:

=IFERROR(INDEX(C$1:N$1,AGGREGATE(15,6,(COLUMN(C$1:N$1)-COLUMN(C$1)+1)/(A$2:A$19=P2)/(C$2:N$19<>"")/(C$2:N$19<>0),COUNTIFS(P$2:P2,P2))),"")

Hi Peter, sorry I should have specified. You are correct in that it`s reading from a dynamically calculated matrix. I tried <>0, and <>"", both of which return the row and column headers corresponding to the A1 position of the matrix. It`s as if without a specific value to search for it just looks to the first poition and says "I found it". This is a challenging problem for sure, the equations above are the closest I`ve seen.