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

 

[PrettyGood_Not Great]

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



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?

Hi Peter, column I can contain anything, this is a variable input field. It can be just numbers, more than likely in sequence, or it can be an alpha numerical combination. Do you think this might change the solution? Using the solution we have thus far works in both cases, it seems to be robust.

 

[Peter_SSs]

Do you think this might change the solution?

Over-simplifying sample data certainly can lead to suggested solutions that work with the simple sample data but not with the real data, so best to be aware of the actual situation.

The only way I can see to do this in a single column with your Excel version (& without vba) would require a delimiter between the row labels and column labels and is a very long formula. In my mind, not really worth it.

23 12 10.xlsm
	G	H	I	J	K	L	M	N	O	P
1				#	A	B	C	D	E	F
2	abc|F			1
3	12|A			2	0	0	0	0	0	0
4	12|C			3	0	0	0	0	0	0
5	12|D			abc	0	0	0	0	0	23
6	yy-9|C			5	0	0	0	0	0	0
7	1|D			ab-23
8	1|E			6	0	0	0	0	0	0
9	y|C			2	0	0	0	0	0	0
10	y|E			12	25	0	125.15	127.5	0	0
11				yy-9	0	0	10	0	0	0
12				1	0	0	0	0.25	25	0
13				y	0	0	0.4	0	15	0
List (4)

Cell Formulas
Range	Formula
G2:G13	G2	=IFERROR(INDEX(J$2:J$13,AGGREGATE(15,6,(ROW(J$2:J$13)-ROW(J$2)+1)/((K$2:P$13<>0)*(K$2:P$13<>"")),ROWS(D$2:D2)))&"|"&INDEX($K$1:$P$1,AGGREGATE(15,6,(COLUMN($K$1:$P$1)-COLUMN($K$1)+1)/($J$2:$J$13=D2)/((K$2:P$13<>0)*(K$2:P$13<>"")),COUNTIFS(G$1:G1,INDEX(J$2:J$13,AGGREGATE(15,6,(ROW(J$2:J$13)-ROW(J$2)+1)/((K$2:P$13<>0)*(K$2:P$13<>"")),ROWS(D$2:D2)))&"|*")+1)),"")

 

[PrettyGood_Not Great]

Over-simplifying sample data certainly can lead to suggested solutions that work with the simple sample data but not with the real data, so best to be aware of the actual situation.

The only way I can see to do this in a single column with your Excel version (& without vba) would require a delimiter between the row labels and column labels and is a very long formula. In my mind, not really worth it.

23 12 10.xlsm
	G	H	I	J	K	L	M	N	O	P
1				#	A	B	C	D	E	F
2	abc|F			1
3	12|A			2	0	0	0	0	0	0
4	12|C			3	0	0	0	0	0	0
5	12|D			abc	0	0	0	0	0	23
6	yy-9|C			5	0	0	0	0	0	0
7	1|D			ab-23
8	1|E			6	0	0	0	0	0	0
9	y|C			2	0	0	0	0	0	0
10	y|E			12	25	0	125.15	127.5	0	0
11				yy-9	0	0	10	0	0	0
12				1	0	0	0	0.25	25	0
13				y	0	0	0.4	0	15	0
List (4)

Cell Formulas
Range	Formula
G2:G13	G2	=IFERROR(INDEX(J$2:J$13,AGGREGATE(15,6,(ROW(J$2:J$13)-ROW(J$2)+1)/((K$2:P$13<>0)*(K$2:P$13<>"")),ROWS(D$2:D2)))&"|"&INDEX($K$1:$P$1,AGGREGATE(15,6,(COLUMN($K$1:$P$1)-COLUMN($K$1)+1)/($J$2:$J$13=D2)/((K$2:P$13<>0)*(K$2:P$13<>"")),COUNTIFS(G$1:G1,INDEX(J$2:J$13,AGGREGATE(15,6,(ROW(J$2:J$13)-ROW(J$2)+1)/((K$2:P$13<>0)*(K$2:P$13<>"")),ROWS(D$2:D2)))&"|*")+1)),"")

Hi Peter, Thank you very much for the attempt, and although I`m ok with the big formula the use of the delimiter would cause an issue for what i`m trying to achieve in the end. Curious, what version of excel would be needed to solve this problem more elegantly? p.s pleasure working with you! For your reference, the first solution you provided took a large, dynamic and tightly integrated file from nearly, to fully automated. Really big thanks, I gained new knowledge in the process.

 

[Peter_SSs]

the use of the delimiter would cause an issue for what i`m trying to achieve in the end.

OK, fair enough. At least you already have something that works.

Curious, what version of excel would be needed to solve this problem more elegantly?

Well, with MS365 it could be done like this (which only requires this formula to be put into G2 and all the other results automatically 'spill' down the column without the need to copy the formula down)

23 12 10.xlsm
	G	H	I	J	K	L	M	N	O	P
1				#	A	B	C	D	E	F
2	abcF			1
3	12A			2	0	0	0	0	0	0
4	12C			3	0	0	0	0	0	0
5	12D			abc	0	0	0	0	0	23
6	yy-9C			5	0	0	0	0	0	0
7	1D			ab-23
8	1E			6	0	0	0	0	0	0
9	yC			2	0	0	0	0	0	0
10	yE			12	25	0	125.15	127.5	0	0
11				yy-9	0	0	10	0	0	0
12				1	0	0	0	0.25	25	0
13				y	0	0	0.4	0	15	0
List (5)

Cell Formulas
Range	Formula
G2:G10	G2	=LET(c,TOCOL(IF(K2:P13="",0,K2:P13)&"|"&J2:J13&K1:P1),TEXTAFTER(FILTER(c,LEFT(c,2)<>"0|"),"|"))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:P13	Expression	=K2=0	text	NO

 

[PrettyGood_Not Great]

OK, fair enough. At least you already have something that works.


Well, with MS365 it could be done like this (which only requires this formula to be put into G2 and all the other results automatically 'spill' down the column without the need to copy the formula down)

23 12 10.xlsm
	G	H	I	J	K	L	M	N	O	P
1				#	A	B	C	D	E	F
2	abcF			1
3	12A			2	0	0	0	0	0	0
4	12C			3	0	0	0	0	0	0
5	12D			abc	0	0	0	0	0	23
6	yy-9C			5	0	0	0	0	0	0
7	1D			ab-23
8	1E			6	0	0	0	0	0	0
9	yC			2	0	0	0	0	0	0
10	yE			12	25	0	125.15	127.5	0	0
11				yy-9	0	0	10	0	0	0
12				1	0	0	0	0.25	25	0
13				y	0	0	0.4	0	15	0
List (5)

Cell Formulas
Range	Formula
G2:G10	G2	=LET(c,TOCOL(IF(K2:P13="",0,K2:P13)&"|"&J2:J13&K1:P1),TEXTAFTER(FILTER(c,LEFT(c,2)<>"0|"),"|"))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2:P13	Expression	=K2=0	text	NO

Hi Peter, now that I have 365 I put this solution to use. works perfect. I have a follow on.

I would like to modify the file's architecture such that the header row (A,B,C etc....which in practice is actually a four character string ie. A1C1, B2C1, etc), to only contain the first two characters in my string (simplifies the larger file structure). The last two characters are still needed for the operation however, and am now trying to dynamically reference these from another list, within your solution.

I was able to make the following change and have the equation continue to work while the header continues to contain all four characters, but when I set the header to have only two, the equation no longer returns any value.

LET(c,TOCOL(IF(Table!AZ3:CW152="",0,Table!AZ3:CW152)&"|"&Table!AY3:AY152&TOROW(SkillCode&TBAavg)),TEXTAFTER(FILTER(c,LEFT(c,2)<>"0|"),"|"))

Here SkillCode and TBAavg are individual vertical lists that when combined form the header row (A,B,C etc in this example). There is something about this equation I don't fully understand it seems, noteably the (LEFT,2) in the Filter. As written my modified equation doesn't actually reference the header row directly, so two questions:
1. Why would the equation continue to return correct values when no longer directly referencing the header row?
2. What is the FILTER(c, LEFT(c,2) doing exactly?

In summary, I want to this solution to perform the same operation as previous, but now Concatenate/Textjoin additional characters to the header characters, dynamically from a vertical list.

 

[Peter_SSs]

Some new small dummy sample data and the expected results with XL2BB and explain again in relation to the new sample data?

 

[PrettyGood_Not Great]

Some new small dummy sample data and the expected results with XL2BB and explain again in relation to the new sample data?

Hi Peter, so after constructing the mini sheet I see that there is in fact no issue with either your solution or the modifications I made. So I have to conclude there is something else causing my problems. Could you have a quick look at this mini sheet, specifically my use of the VSTACK(FILTER() to see if you can find any common pitfalls? i.e. maybe something stands out to you that if used in certain conditions would need error handling, or something of this nature.

With this exact equation used in my file it is returning either the Data set 1, or dataset 2, but not both. If I use just your LET() then I return data set 2, If I use the equation as shown, then it only returns data set1. The only thing different about this mini sheet and the actual file is that some of these Named ranges, but I can't imagine this is the issue.

SORI_Labour_Planning_M365_v1.6.0.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	CC	Code	Value	Data set 1	List	Data set 2	CC	AA	AG	AK	BA	BC	From Data set 2	Current solution	Combine both data sets	combine
2	1234	AA	A1		2134AAA1		1234	23	20	0	10	4		1234AAA1		2134AAA1
3	1235	AG	A2		2135ACB2		1235	15	20	14	0	4		1234AGA2		2135ACB2
4	1236	AK	A3		2136BBG3		1236	29	30	16	0	4		1234BAA3		2136BBG3
5	1237	BA	A3		2137DDK6		1237	0	0	0	0	0		1234BCA2		2137DDK6
6	1238	BC	A2		2138ZZD3									1235AAA1		2138ZZD3
7														1235AGA2		1234AAA1
8														1235AKA3		1234AGA2
9														1235BCA2		1234BAA3
10														1236AAA1		1234BCA2
11														1236AGA2		1235AAA1
12														1236AKA3		1235AGA2
13														1236BCA2		1235AKA3
14																1235BCA2
15																1236AAA1
16																1236AGA2
17																1236AKA3
18																1236BCA2
Sheet1

Cell Formulas
Range	Formula
H1:L1	H1	=TOROW(B2:B6)
N2:N13	N2	=LET(c,TOCOL(IF(H2:L5="",0,H2:L5)&"|"&G2:G5&TOROW(B2:B6&C2:C6)),TEXTAFTER(FILTER(c,LEFT(c,2)<>"0|"),"|"))
P2:P18	P2	=UNIQUE(VSTACK(FILTER(E2:E9,E2:E9<>""),LET(c,TOCOL(IF(H2:L5="",0,H2:L5)&"|"&G2:G5&TOROW(B2:B6&C2:C6)),TEXTAFTER(FILTER(c,LEFT(c,2)<>"0|"),"|"))))
G2:G5	G2	=A2
Dynamic array formulas.