return row and column headers for matrix for duplicate values

DeonRobbie

New Member
Joined
Oct 8, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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?


1633694320633.png

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



:confused: 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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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
GHIJKLMNOP
1#ABCDEF
2abc|F1
312|A2000000
412|C3000000
512|Dabc0000023
6yy-9|C5000000
71|Dab-23
81|E6000000
9y|C2000000
10y|E12250125.15127.500
11 yy-90010000
12 10000.25250
13 y000.40150
List (4)
Cell Formulas
RangeFormula
G2:G13G2=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)),"")
 
Upvote 0
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
GHIJKLMNOP
1#ABCDEF
2abc|F1
312|A2000000
412|C3000000
512|Dabc0000023
6yy-9|C5000000
71|Dab-23
81|E6000000
9y|C2000000
10y|E12250125.15127.500
11 yy-90010000
12 10000.25250
13 y000.40150
List (4)
Cell Formulas
RangeFormula
G2:G13G2=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.
 
Upvote 0
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. (y) :)

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
GHIJKLMNOP
1#ABCDEF
2abcF1
312A2000000
412C3000000
512Dabc0000023
6yy-9C5000000
71Dab-23
81E6000000
9yC2000000
10yE12250125.15127.500
11yy-90010000
1210000.25250
13y000.40150
List (5)
Cell Formulas
RangeFormula
G2:G10G2=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
CellConditionCell FormatStop If True
K2:P13Expression=K2=0textNO
 
Upvote 0
OK, fair enough. At least you already have something that works. (y) :)


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
GHIJKLMNOP
1#ABCDEF
2abcF1
312A2000000
412C3000000
512Dabc0000023
6yy-9C5000000
71Dab-23
81E6000000
9yC2000000
10yE12250125.15127.500
11yy-90010000
1210000.25250
13y000.40150
List (5)
Cell Formulas
RangeFormula
G2:G10G2=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
CellConditionCell FormatStop If True
K2:P13Expression=K2=0textNO
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.
 
Upvote 0
Some new small dummy sample data and the expected results with XL2BB and explain again in relation to the new sample data?
 
Upvote 0
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.

Book1
ABCDEFGHIJKLMNOP
1CCCodeValueData set 1ListData set 2CCAAAGAKBABCFrom Data set 2Current solutionCombine both data setscombine
21234AAA12134AAA11234232001041234AAA12134AAA1
31235AGA22135ACB21235152014041234AGA22135ACB2
41236AKA32136BBG31236293016041234BAA32136BBG3
51237BAA32137DDK61237000001234BCA22137DDK6
61238BCA22138ZZD31235AAA12138ZZD3
71235AGA21234AAA1
81235AKA31234AGA2
91235BCA21234BAA3
101236AAA11234BCA2
111236AGA21235AAA1
121236AKA31235AGA2
131236BCA21235AKA3
141235BCA2
151236AAA1
161236AGA2
171236AKA3
181236BCA2
Sheet1
Cell Formulas
RangeFormula
H1:L1H1=TOROW(B2:B6)
N2:N13N2=LET(c,TOCOL(IF(H2:L5="",0,H2:L5)&"|"&G2:G5&TOROW(B2:B6&C2:C6)),TEXTAFTER(FILTER(c,LEFT(c,2)<>"0|"),"|"))
P2:P18P2=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:G5G2=A2
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top