-1

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.

https://docs.google.com/spreadsheets/d/1UC0cgsVCm0ekbtu7Wsjpy8PdJ76o8HNq/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

MmVv
  • 553
  • 6
  • 22
  • You have asked the same question 3 times in the past two weeks. There is no need to keep reposting it – horseyride Apr 25 '22 at 14:50
  • @horseyride They are alle deleted anyways. I put bit more effort to write it more understandable this time. – MmVv Apr 25 '22 at 14:59
  • When you get a response you don't like, or in this case no one had an answer, deleting and reposting the same question over and over in different variations does not seem the best way to go – horseyride Apr 25 '22 at 15:04
  • @horseyride Sorry, of 8billion persons, someone might respond. But this was last post on this topic. – MmVv Apr 25 '22 at 15:06
  • Also tagging both [Excel 2016] and [Excel 365] is confusing. Office365 contains a lot of functionalities that will not work in Excel 2016. If you want us to reproduce something, do you expect us to type it all out? Or do you think you can reduce your data to a sample size (less columns, less rows) and sample desired result in that data? – P.b Apr 25 '22 at 15:13
  • @P.b it is already reduced. Otherwise not possible to understand the problem – MmVv Apr 25 '22 at 15:18
  • 3
    What about using `=SUM(--(INDEX(_0359_matrix[#All],XMATCH($A$9:$K$9,_0359_matrix[[#All],[_]]),XMATCH(INDEX($A$9:$K$9,,INT(ROW()/3)-6),_0359_matrix[#Headers]))="1"))>0` for conditional formatting `A21:A54` then there's no need for `A13:K13` use the same logic for column G. – P.b Apr 25 '22 at 19:04
  • @P.b Hmm interesting approach, seems to work but small correction, there where cond.formatting is, but those fields have to be there exactly as they are. That is a reason I put boolean in another place..but maybe I can put a formula above the row headers so A20, A23 and so on.. – MmVv Apr 25 '22 at 21:09
  • @P.b But wait a sec, can you adjust your functions to fit in A13:K14 rows? In your function $A$9:$K$9 is fine for one line of codes, but I got two... trying to understand this part: INT(ROW()/3)-6) – MmVv Apr 25 '22 at 21:27
  • The `INT` part is to create a counter to start at 1 at row `A21` and results in 2 at A24, etc. – P.b Apr 26 '22 at 05:52
  • @P.b Thanks, I took only that part of function and examined it, exactly, very useful combo. – MmVv Apr 26 '22 at 06:32
  • @P.b Strange that your formulas are fine for only one example, but if I try to adjsut it on the rest of workbooks, it gives me #NA error. Whatver I change still. – MmVv Apr 26 '22 at 15:28

1 Answers1

2

The formula below could be used in cell A13 and then copied across

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

and the formula below could be used in cell A14 and then copied across

=SUMPRODUCT((_0359_matrix[_]=A12)*(IFNA(IF(MATCH(COLUMN(_0359_matrix[[#Headers],[070FX]:[YS1]])-1,MATCH($A$9:$K$9,_0359_matrix[[#Headers],[070FX]:[YS1]],0),0),1,0),0)+IFNA(IF(MATCH(COLUMN(_0359_matrix[[#Headers],[070FX]:[YS1]])-1,MATCH($A$12:$K$12,_0359_matrix[[#Headers],[070FX]:[YS1]],0),0),1,0),0))*(NOT(ISERROR(1/VALUE(_0359_matrix[[070FX]:[YS1]])=1))))>0

Both formulae are longer than they could be, since they're using table referencing but, since you wanted your formulae to be dynamic, I think it's appropriate (without table referencing the formulae are approximately half the length of the originals).

The formulae are just shorter versions of your originals but there is a LOT of duplication of calculations in both sets, i.e. ALL 22 cells calculate the mid portion (the middle multiplicand) and the last portion (the last multiplicand) so, for the sake of performance, it would be sensible to have 2 helper cells (or 2 named formulae) which calculate these values once, and then just have the 22 formulae referring to these, thus shortening the formulae considerably.

(the table referencing may, or may not, make the formula less readable so I'm including below a screenshot of my 'research' 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)

Spectral Instance
  • 1,971
  • 2
  • 3
  • 6
  • Pew..that is hell of a function =) but I did quick peek and test, seems to work on my dummy...let me test it during the day and I will write my comment. Thanks in advance! – MmVv Apr 26 '22 at 06:31
  • My research was undertaken when I should have been asleep, i.e. the 'aggregate match' formula wasn't as short as it could have been - I also added example of how short the formula could be made, if all 22 inputs were 'consolidated' in a single row – Spectral Instance Apr 26 '22 at 13:02
  • I tried all of your solutions, and they all work, SUMPRODUCT solution is good to, it is shorter too. This with LET is also great but one thing, you said like it is fine to have them just "imaginary referenced" in whole row but still visually presented as they are on my page? Not sure how I can print it out without noticing in anything is there. Will need to check it out. But I will mark your answer as fine, because I got enough material to build it =) Thanks a lot! – MmVv Apr 26 '22 at 15:41
  • You could actually have the 22 'reference cells' anywhere, e.g. on one of your hidden sheets. The 'advantage' of extending them to V9 on Test 1(and then hiding columns L through V to preserve the 'print view') is that the hidden columns on that sheet could act as a reminder, given that your template undergoes lots of updates, that you might need to update them (if you put them on a hidden sheet its possible that you might forget to make any necessary updates to them). – Spectral Instance Apr 26 '22 at 15:50
  • Ah wait a sec, x[[#Headers],[070FX]:[YS1]] this is defined (almost hard-coded) with structured referencing, but what if I add (expand) new columns (YS2 or ZF0) in both vert/horiz? I tried that and this range ([070FX]:[YS1]]) remained the same....so it brings me back to my hard-coded arrays or? – MmVv Apr 27 '22 at 12:38
  • In the shortest version of the formula, [070FX]:[YS1], appears a total of 2 times - adjusting only 2 references manually is a lot simpler than the 22 in your original formula. At the expense of formula length, it would be possible to make those aspects dynamic, but only you/your bosses know how the template will evolve, e.g. you have a current inconsistency of having formulae (A12:K12) reading the matrix, but those columns are excluded from the matrix - only you know if the 'rule' will always be to exclude the last 3 columns – Spectral Instance Apr 27 '22 at 12:49
  • Of course, yea, I jsut wanted to ask. But it is not that is going to be changed monthly or so, but maybe sometimes and that has to be noted. Your help is very much appreciated, I am going to adjust my docs. Thanks – MmVv Apr 27 '22 at 12:57
  • Making the formula dynamic for columns to be omit(ted) makes the formula over 50% longer: =LET(omit,3,hdr_,_0359_matrix[#Headers],headers,INDEX(hdr_,,SEQUENCE(1,COLUMNS(hdr_)-omit-1,2)),dat_,_0359_matrix[#Data],matrix,INDEX(dat_,SEQUENCE(ROWS(dat_),),SEQUENCE(,COLUMNS(dat_)-omit-1,2)),SUMPRODUCT((_0359_matrix[_]=A9)*(IFNA(IF(MATCH(SEQUENCE(1,COLUMNS(headers)),MATCH($A$9:$V$9,headers,0),0),1,0),0))*(NOT(ISERROR(1/VALUE(matrix)=1))))>0) - flexibility at the expense of comprehension(!) – Spectral Instance Apr 27 '22 at 19:45
  • If you really don't want to make virtual copies of row 12 of Test1 then you could make a virtual row of rows 9 & 12 together with INDEX(FILTERXML(""&TEXTJOIN("",TRUE,A9:K9,A12:K12)&"","//e"),SEQUENCE(1,21)) – Spectral Instance Apr 27 '22 at 19:47
  • Oh man...that first comment and that formula gave me headache :/ but this with FILTERXML seems interesting though...so that means I can just put it in A9, but still gives me in SEQUENCE function more columns that are out of my "page". – MmVv Apr 28 '22 at 12:16
  • The idea of the INDEX/FILTERXML was so that you don't have to create a 'virtual range' on any actual worksheet - of course, if you did use it, then you would make the formula much longer again... – Spectral Instance Apr 28 '22 at 12:19
  • I will stick to LET solution, considered as best and easier to understand :D – MmVv Apr 28 '22 at 12:29
  • @Mirza - here are almost 50% shorter* formulae (i) for `A13`, `=SUMPRODUCT((_0359_matrix[_]=A9)*(COUNTIF($A$9:$K$9,_0359_matrix[[#Headers],[070FX]:[YS1]])+COUNTIF($A$12:$K$12,_0359_matrix[[#Headers],[070FX]:[YS1]]))*(_0359_matrix[[070FX]:[YS1]]="1"))>0` and (ii) for `A14`, `=SUMPRODUCT((_0359_matrix[_]=A12)*(COUNTIF($A$9:$K$9,_0359_matrix[[#Headers],[070FX]:[YS1]])+COUNTIF($A$12:$K$12,_0359_matrix[[#Headers],[070FX]:[YS1]]))*(_0359_matrix[[070FX]:[YS1]]="1"))>0` *as against originally-suggested formulae – Spectral Instance Jun 14 '22 at 23:18