CHALLENGE: find column formula for validation of mandatory columns in a table with user data

Copper Contributor

You are challenged to come up with a solution for the problem described here:

tomsuters_0-1675465972770.png

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!

7 Replies

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

@tomsuters 

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)))

@tomsuters 

 

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

 

@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.

@mtarler 

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

yes that is simpler but I think they required it to work if they added columns and I believe by defining [col1]:[col4] that when you add col5 it doesn't automatically get included. I have done solutions like that where I use [start]:[end] where start and end are 'dummy' columns and tell the user they must insert columns between them if they want them added in.

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

@tomsuters