Feb 03 2023 03:27 PM - edited Feb 03 2023 03:47 PM
You are challenged to come up with a solution for the problem described here:
My first attempt was to try and use an array formula of the following form:
{=SOM(N(ISEMPTY(INDIRECT(<a list of cell references to the mandatory cells in a row>)))=0}
I failed as it turned out that EXCEL does not allow the INDIRECT() function to be used in array formulas and you need INDIRECT() to create the list of references to the mandatory cells in a row of table 2 from the column names in table 1.
I found a similar task with the same problem here: excel - Is it possible to have array as an argument to INDIRECT(), so INDIRECT() returns array? - St...
I look forward to your suggestions. Please bear in mind that the formula should not be changed if the content of table 1 is changed!
Feb 03 2023 11:02 PM - edited Feb 03 2023 11:03 PM
Can you clarify why you have marked the third row in Table 2 as TRUE? That row has 'user data' in col1, col2 and col4? Should not col3 contain 'user data'?
Also, I assume from your previous posts that any solution must be for pre-O365 versions of Excel?
Regards
Feb 03 2023 11:15 PM
It's not clear why you would use INDIRECT().
Maybe I don't understand the 'challenge'.
=LET(
t,table_2,
r,INDEX(t,ROW()-ROW(table_2[#Headers]),0),
a,FILTER(r,COUNTIFS(table_1[mand cols],table_2[#Headers])),
b,FILTER(a,a<>0),
c,COUNTA(b)=COUNTA(table_1[mand cols]),
IF(COUNTA(b)>1,c,IF(ISERROR(b),FALSE,c)))
Feb 04 2023 12:07 AM - edited Feb 04 2023 12:10 AM
Also, I'm not sure from where all the confusion/misunderstanding surrounding INDIRECT not processing an array derives. Even in legacy versions of Excel, that function is perfectly capable of processing an array: the example in the link you posted, for example, is solved by the simple insertion of the N function, i.e.
=SUM(N(INDIRECT(A1:A5)))
If the entries in A1:A5 are text, not numeric, then replace N with T (obviously in that case applying a SUM function would make little sense; nevertheless, the point remains that an array of returns is coerced). Even for a range comprising mixed datatypes it is possible to coerce the array appropriately.
Regards
Feb 04 2023 02:15 PM
@JosWoolley In a previous post, we found some odd behavior when using INDIRECT and ROW(). Personally I try to avoid INDIRECT as much as possible.
@Detlef Lewin I didn't go through your solution but I believe @tomsuters needs a 'legacy' formula
@tomsuters check the attached. you may need to use CSE (ctrl-shift-enter)
=SUM(
IF(ISNUMBER(MATCH(INDEX(Table2[#Headers],COLUMN($B$1:INDEX($1:$1,COLUMNS(Table2)))),Table1,0)),
--ISNUMBER(SEARCH("empty",INDEX(Table2,ROW()-ROW(Table2[#Headers]),COLUMN($B$1:INDEX($1:$1,COLUMNS(Table2)))))),
0))=0
I wasn't sure what <empty> meant so I took it literal and searched for "empty" as a "pass" and any pass would result in a FALSE but you could use ISBLANK instead of the ISNUMBER(SEARCH(... )) if it is actually blank like this:
=SUM(
IF(ISNUMBER(MATCH(INDEX(Table24[#Headers],COLUMN($B$1:INDEX($1:$1,COLUMNS(Table24)))),Table1,0)),
--ISBLANK(INDEX(Table24,ROW()-ROW(Table24[#Headers]),COLUMN($B$1:INDEX($1:$1,COLUMNS(Table24))))),
0))=0
I included both versions in the attached.
This all said, I have the new Excel with dynamic arrays and such so can't test if it will work in a legacy version of Excel.
Feb 04 2023 10:34 PM - edited Feb 04 2023 10:45 PM
Yes, I already replied to that previous post.
As to the current problem, the following might be even simpler:
=SUM(N(INDEX(
Table2[@[col1]:[col4]],
N(IF(1, MATCH(Table1, Table2[[#Headers],[col1]:[col4]], 0)))
) = "")
) = 0
Regards
Feb 05 2023 07:52 AM
Feb 09 2023 06:06 PM
dear @mtarler @JosWoolley @Detlef Lewin
just to let you know I am still looking into this and will get back to you in the coming days with an extensive analysis. Conclusion so far is that I still dhave not seen or found a solution.
Regards