Home > Software design >  Matching all combinations (values) from matrix-like-table using drop-down
Matching all combinations (values) from matrix-like-table using drop-down

Time:04-27

On my workbook (WB) there are 2x sheets. One is Test1 or where I have my drop-downs (from A22 and below) and on A8-A11 are matching fields that are being colored if correspoding match is "hit". In my case FALSE is (_) and TRUE (1) if you are looking on Matrix table on sheet2.

enter image description here

On sheet 2 (Matrix) is matrix-table that has horizontal/vertical axis same (headers), and (_'s) (1's) if there is an interesection. Meaning, it has to be all FALSE so that the System is in "green (all FALSE)" and can be sold, if only one part is "red (TRUE)" then the combination is not supported.

enter image description here

enter image description here

Example from matrix:

070FX has (_) on intersect has (1) on CE0, D01 and it that case it should hit TRUE. So all three parts should be "red (TRUE)", as it is on 3rd picture.

If you check my TRUE/FALSE results from formula (in A13 and A14) you can understand it slightly better:

=SUMPRODUCT((Matrix!$A$2:$A$103=A12)*((Matrix!$B$1:$CV$1=$A$9) (Matrix!$B$1:$CV$1=$B$9) (Matrix!$B$1:$CV$1=$C$9) (Matrix!$B$1:$CV$1=$D$9) (Matrix!$B$1:$CV$1=$E$9) (Matrix!$B$1:$CV$1=$F$9) (Matrix!$B$1:$CV$1=$G$9) (Matrix!$B$1:$CV$1=$H$9) (Matrix!$B$1:$CV$1=$I$9) (Matrix!$B$1:$CV$1=$J$9) (Matrix!$B$1:$CV$1=$K$9) (Matrix!$B$1:$CV$1=$A$12) (Matrix!$B$1:$CV$1=$B$12) (Matrix!$B$1:$CV$1=$C$12) (Matrix!$B$1:$CV$1=$D$12) (Matrix!$B$1:$CV$1=$E$12) (Matrix!$B$1:$CV$1=$F$12) (Matrix!$B$1:$CV$1=$G$12) (Matrix!$B$1:$CV$1=$I$12) (Matrix!$B$1:$CV$1=$J$12) (Matrix!$B$1:$CV$1=$K$12))*(NOT(ISERROR(1/VALUE(Matrix!$B$2:$CV$103)=1))))>0

Maybe you are asking why two rows of formulas (A13 and A14), it is actually one formula but I separated into two rows because of printing, this document should fit on one page only.

*The problem what I have is making this more dynamic, and easier to read/understand. If you see my formula it is SUMPRODUCT but it does have hard-coded arrays, and that is not what I need, I realise recently that we have many changes within our document and sometimes parts are being added or deleted. But my array is hard-coded, so you can imagine how much effort is to adjust it. And to explain to someone how it works is also pain in the ss.

I hope there is some different way to do this, maybe another set of functions or even with Power Query as best dynamic thing in Excel.

Research undertaken for answer

the data in row 1 are proxies for your table headers, and the data in row 3 are proxies for your row 9 (of Test1) - the formula effectively does all the matching for a single row in aggregate, rather than having to sum individual results, as was the case in your original formulae; in this way there are 2 'copies' of the formula, since your headers (on Test1) are on 2 different rows due to your 'printing constraint' - you could make my formula shorter if all headers were in a single row, e.g. by putting the formula =A12 in cell L9, =B12 in M9 etc (and possibly hiding those columns, to keep the sheet 'clean') - if you implemented this suggestion, then the formula for cell A13 could be shortened to this

=LET(hdrs,_0359_matrix[[#Headers],[070FX]:[YS1]],SUMPRODUCT((_0359_matrix[_]=A9)*(IFNA(IF(MATCH(COLUMN(hdrs)-1,MATCH($A$9:$V$9,hdrs,0),0),1,0),0))*(NOT(ISERROR(1/VALUE(_0359_matrix[[070FX]:[YS1]])=1))))>0)

which is less than 1/3 the length of your original formula)

  • Related