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

 

[Peter_SSs]

the equations above are the closest I`ve seen.

Are you saying that the latest formulas still do not work?
If so, could you please provide a sample of the table shown in post 1 with XL2BB so that we can see the formulas and copy the values that you actually have rather than the ones that we might make up for ourselves?

 

[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))),"")

(Picture removed)

Hi Peter,
Sorry for the delay. The Array I1:O13 has no equations on it.

 

[Peter_SSs]

You are correct in that it`s reading from a dynamically calculated matrix.

The Array I1:O13 has no equations on it.

Those two statements seem to contradict each other.

In any case, we cannot tell much from a picture. Can you give us that information shown in post #12 with XL2BB instead of an image? Then we can test with the same data that you are testing with and might be able to work out what is happening.

 

[PrettyGood_Not Great]

Those two statements seem to contradict each other.

In any case, we cannot tell much from a picture. Can you give us that information shown in post #12 with XL2BB instead of an image? Then we can test with the same data that you are testing with and might be able to work out what is happening.

Hi Peter, mini sheet below. The picture above was created an example file with made up data and no equations to troubleshoot this one. The operational file does have equations in the range. The equations being attempted here don`t work in either case.

 

[PrettyGood_Not Great]

Those two statements seem to contradict each other.

In any case, we cannot tell much from a picture. Can you give us that information shown in post #12 with XL2BB instead of an image? Then we can test with the same data that you are testing with and might be able to work out what is happening.

Hi Peter, lets use this as our example. We have a static array, and an example of using these equations to look for a specific result, 25 in this case, and an example of using <>"" or <>0 (same results using either criteria).

book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	`= a specific integer (25)				`= <>0 and/or <>""				#	A	B	C	D	E	F
2	1	A	1A		1	A	1A		1
3	1	E	1E		1	A	1A		1
4					1	A	1A		1
5					1	B	1B		1
6					1	B	1B		1
7					1	C	1C		1
8					1	C	1C		1
9					1	C	1C		1
10					1	C	1C		1	25		125.15	127.5
11					1	C	1C		1			10
12					1	D	1D		1				0.25	25
13					1	D	1D		1			0.4		15
14					1	D	1D
Sheet1

Cell Formulas
Range	Formula
A2	A2	=IFERROR(INDEX($I$2:$I$13,AGGREGATE(15,6,(ROW($I$2:$I$13)-ROW($I$2)+1)/($J$2:$O$13=25),ROWS(A2:A$2))),"")
B2	B2	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=A2)/($J$2:$O$13=25),COUNTIFS(A2:A$2,A2))),"")
G2:G14,C2:C14	C2	=A2&B2
A3:A14	A3	=IFERROR(INDEX($I$2:$I$13,AGGREGATE(15,6,(ROW($I$2:$I$13)-ROW($I$2)+1)/($J$2:$O$13=25),ROWS(A$2:A3))),"")
B3:B14	B3	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=A3)/($J$2:$O$13=25),COUNTIFS(A$2:A3,A3))),"")
E2	E2	=IFERROR(INDEX($I$2:$I$13,AGGREGATE(15,6,(ROW($I$2:$I$13)-ROW($I$2)+1)/($J$2:$O$13<>0),ROWS(E2:E$2))),"")
F2	F2	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E2)/($J$2:$O$13<>0),COUNTIFS(E2:E$2,E2))),"")
E3:E14	E3	=IFERROR(INDEX($I$2:$I$13,AGGREGATE(15,6,(ROW($I$2:$I$13)-ROW($I$2)+1)/($J$2:$O$13<>0),ROWS(E$2:E3))),"")
F3:F14	F3	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E3)/($J$2:$O$13<>0),COUNTIFS(E$2:E3,E3))),"")

 

[Peter_SSs]

Firstly, most of those "empty" cells in J2:O13 are not empty but contain zero values, presumably hidden by Custom cell formatting or Conditional Formatting. The only ones that appear empty and do not contain zero are J2:O2 and J7:O7

Secondly, I think that the cells J2:O2 and J7:O7 probably previously had formulas that resulted in "" in them and those cells (probably along with the rest in that grid) have had a Copy/Paste Values applied to them. That would mean those cells actually contain a null string and are thus also not really empty.

Anyway, see how this goes.

23 12 10.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1	`= <>0 and <>""				#	A	B	C	D	E	F
2	1	A	1A		1
3	1	C	1C		1	0	0	0	0	0	0
4	1	C	1C		1	0	0	0	0	0	0
5	1	C	1C		1	0	0	0	0	0	0
6	1	D	1D		1	0	0	0	0	0	0
7	1	D	1D		1
8	1	E	1E		1	0	0	0	0	0	0
9	1	E	1E		1	0	0	0	0	0	0
10					1	25	0	125.15	127.5	0	0
11					1	0	0	10	0	0	0
12					1	0	0	0	0.25	25	0
13					1	0	0	0.4	0	15	0
List

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(I$2:I$13,AGGREGATE(15,6,(ROW(I$2:I$13)-ROW(I$2)+1)/((J$2:O$13<>0)*(J$2:O$13<>"")),ROWS(E2:E$2))),"")
F2	F2	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E2)/((J$2:O$13<>0)*(J$2:O$13<>"")),COUNTIFS(E2:E$2,E2))),"")
G2:G13	G2	=E2&F2
E3:E13	E3	=IFERROR(INDEX(I$2:I$13,AGGREGATE(15,6,(ROW(I$2:I$13)-ROW(I$2)+1)/((J$2:O$13<>0)*(J$2:O$13<>"")),ROWS(E$2:E3))),"")
F3:F13	F3	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E3)/((J$2:O$13<>0)*(J$2:O$13<>"")),COUNTIFS(E$2:E3,E3))),"")

 

[PrettyGood_Not Great]

Firstly, most of those "empty" cells in J2:O13 are not empty but contain zero values, presumably hidden by Custom cell formatting or Conditional Formatting. The only ones that appear empty and do not contain zero are J2:O2 and J7:O7

Secondly, I think that the cells J2:O2 and J7:O7 probably previously had formulas that resulted in "" in them and those cells (probably along with the rest in that grid) have had a Copy/Paste Values applied to them. That would mean those cells actually contain a null string and are thus also not really empty.

Anyway, see how this goes.

23 12 10.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1	`= <>0 and <>""				#	A	B	C	D	E	F
2	1	A	1A		1
3	1	C	1C		1	0	0	0	0	0	0
4	1	C	1C		1	0	0	0	0	0	0
5	1	C	1C		1	0	0	0	0	0	0
6	1	D	1D		1	0	0	0	0	0	0
7	1	D	1D		1
8	1	E	1E		1	0	0	0	0	0	0
9	1	E	1E		1	0	0	0	0	0	0
10					1	25	0	125.15	127.5	0	0
11					1	0	0	10	0	0	0
12					1	0	0	0	0.25	25	0
13					1	0	0	0.4	0	15	0
List

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(I$2:I$13,AGGREGATE(15,6,(ROW(I$2:I$13)-ROW(I$2)+1)/((J$2:O$13<>0)*(J$2:O$13<>"")),ROWS(E2:E$2))),"")
F2	F2	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E2)/((J$2:O$13<>0)*(J$2:O$13<>"")),COUNTIFS(E2:E$2,E2))),"")
G2:G13	G2	=E2&F2
E3:E13	E3	=IFERROR(INDEX(I$2:I$13,AGGREGATE(15,6,(ROW(I$2:I$13)-ROW(I$2)+1)/((J$2:O$13<>0)*(J$2:O$13<>"")),ROWS(E$2:E3))),"")
F3:F13	F3	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E3)/((J$2:O$13<>0)*(J$2:O$13<>"")),COUNTIFS(E$2:E3,E3))),"")

Peter, that's it! Outstanding, thank you so much. I feel greedy for asking this, but is it also possible to combine these two equations into one formula such that in a single column I get to the combined number/letter results in column G? Or is that off the table.

 

[Peter_SSs]

Peter, that's it! Outstanding, thank you so much.

You're welcome, but I had a slight error in the row 2 formulas of my previous post. Formulas should have been these. You may already have made the correction.

23 12 10.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1	`= <>0 and <>""				#	A	B	C	D	E	F
2	1	A	1A		1
3	1	C	1C		1	0	0	0	0	0	0
4	1	C	1C		1	0	0	0	0	0	0
5	1	C	1C		1	0	0	0	0	0	0
6	1	D	1D		1	0	0	0	0	0	0
7	1	D	1D		1
8	1	E	1E		1	0	0	0	0	0	0
9	1	E	1E		1	0	0	0	0	0	0
10					1	25	0	125.15	127.5	0	0
11					1	0	0	10	0	0	0
12					1	0	0	0	0.25	25	0
13					1	0	0	0.4	0	15	0
List

Cell Formulas
Range	Formula
E2:E13	E2	=IFERROR(INDEX(I$2:I$13,AGGREGATE(15,6,(ROW(I$2:I$13)-ROW(I$2)+1)/((J$2:O$13<>0)*(J$2:O$13<>"")),ROWS(E$2:E2))),"")
F2:F13	F2	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E2)/((J$2:O$13<>0)*(J$2:O$13<>"")),COUNTIFS(E$2:E2,E2))),"")
G2:G13	G2	=E2&F2

is it also possible to combine these two equations into one formula such that in a single column I get to the combined number/letter results in column G?

I am unsure. I believe that your column I sample data above is not at all representative of your real data. If I am right, could we have some more realistic sample data (it can still be dummy data but more like the structure of your real data) and the expected results with XL2BB?

 

[PrettyGood_Not Great]

You're welcome, but I had a slight error in the row 2 formulas of my previous post. Formulas should have been these. You may already have made the correction.

23 12 10.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1	`= <>0 and <>""				#	A	B	C	D	E	F
2	1	A	1A		1
3	1	C	1C		1	0	0	0	0	0	0
4	1	C	1C		1	0	0	0	0	0	0
5	1	C	1C		1	0	0	0	0	0	0
6	1	D	1D		1	0	0	0	0	0	0
7	1	D	1D		1
8	1	E	1E		1	0	0	0	0	0	0
9	1	E	1E		1	0	0	0	0	0	0
10					1	25	0	125.15	127.5	0	0
11					1	0	0	10	0	0	0
12					1	0	0	0	0.25	25	0
13					1	0	0	0.4	0	15	0
List

Cell Formulas
Range	Formula
E2:E13	E2	=IFERROR(INDEX(I$2:I$13,AGGREGATE(15,6,(ROW(I$2:I$13)-ROW(I$2)+1)/((J$2:O$13<>0)*(J$2:O$13<>"")),ROWS(E$2:E2))),"")
F2:F13	F2	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$13=E2)/((J$2:O$13<>0)*(J$2:O$13<>"")),COUNTIFS(E$2:E2,E2))),"")
G2:G13	G2	=E2&F2

I am unsure. I believe that your column I sample data above is not at all representative of your real data. If I am right, could we have some more realistic sample data (it can still be dummy data but more like the structure of your real data) and the expected results with XL2BB?

Hi Peter, do you mean for the COUNTIFS ABS reference? Yes I caught that, equations are working as expected. And yes, the column I data was a bit of a typo, it should look like the following. This time I have also left the strings you referenced above in place so we can see if they will ever cause issue. Thus far your solution handles them.

book1.xlsm
	E	F	G	H	I	J	K	L	M	N	O
1	Row	Column	Matrix		#	A	B	C	D	E	F
2	9	A	9A		1
3	9	C	9C		2	0	0	0	0	0	0
4	9	D	9D		3	0	0	0	0	0	0
5	10	C	10C		4	0	0	0	0	0	0
6	11	D	11D		5	0	0	0	0	0	0
7	11	E	11E		6
8	12	C	12C		7	0	0	0	0	0	0
9	12	E	12E		8	0	0	0	0	0	0
10	13	C	13C		9	25	0	125.15	127.5	0	0
11	13	D	13D		10	0	0	10	0	0	0
12	13	E	13E		11	0	0	0	0.25	25	0
13					12	0	0	0.4	0	15	0
14					13	0	0	-80.025	0.25	10	0
Sheet1

Cell Formulas
Range	Formula
E2:E14	E2	=IFERROR(INDEX(I$2:I$14,AGGREGATE(15,6,(ROW(I$2:I$14)-ROW(I$2)+1)/((J$2:O$14<>0)*(J$2:O$14<>"")),ROWS(E$2:E2))),"")
F2:F14	F2	=IFERROR(INDEX($J$1:$O$1,AGGREGATE(15,6,(COLUMN($J$1:$O$1)-COLUMN($J$1)+1)/($I$2:$I$14=E2)/(($J$2:$O$14<>0)*($J$2:$O$14<>"")),COUNTIFS(E$2:E2,E2))),"")
G2:G14	G2	=E2&F2

 

[Peter_SSs]

Hi Peter, do you mean for the COUNTIFS ABS reference?

Yes, and similar in the ROWS() function at the end of the col E formula. Glad you caught them both.

the column I data was a bit of a typo, it should look like the following.

Should it? The data that you requested be removed from the forum was not simple numerical values in col I but rather a combination of letters, numbers and dashes of varying lengths.
Can you clarify what the actual position is (& make up some more realistic dummy sample data if it is not simply numbers in col I?
If they are just numbers in column I, are they a simple sequence of consecutive numbers as shown above?