Google Sheets formula with variable number of disjoint columns

0
votes

In the table I have multiple disjoint columns with similar meaning: “is this row interesting?”

I want to create an array formula to get all interesting rows. How can I approach this?

Example table:

Obj id | Case 1 data | Case 1 interesting? | Case 2 data | Case 2 interesting?
     1 |       …     |        YES          |     …       |       NO
     2 |       …     |         NO          |     …       |       NO
     3 |       …     |         NO          |     …       |      YES
     4 |       …     |         NO          |     …       |       NO
     5 |       …     |        YES          |     …       |      YES
     6 |       …     |         NO          |     …       |       NO

The actual table is split into several sheets with different subsets of ids on each sheet.

My current approach is stuck with INDIRECT function not accepting array or ranges. I first search for my columns: FILTER(COLUMN(A1:1), REGEXMATCH(A1:1, "interesting")), then I convert column addresses to ranges, but when I feed the result to INDIRECT, it only returns the first column.

The desired formula would output an array of unique object ids where each row is interesting for at least one case.

UPDATE: here is a test table for this problem. There are 3 sheets: student's data with ids and 2 programs. Each program has several exams (not known beforehand. The desired formula would output an array of unique student ids with at least 1 Passed exam (in the test sheet: 1, 3, 4, 6)

1
google-sheetsgoogle-sheets-formula
are interesting columns not on fixed/known position? – player0
@player0 no, and their number keeps increasing as new cases are added – Nikolay Markov
from your example, you want only row 5? or 1,3 and 5? – player0
@player0 In this example: 1,3,5. I cannot share the original sheet, but I made a (hopefully) sufficient test example, please see the updated post – Nikolay Markov

1 Answers

1
votes
={"Passing"; ARRAYFORMULA(UNIQUE(QUERY({
 IF(IFERROR(REGEXEXTRACT(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE('Program 1'!A1:Z), , 999^99))), "Pass", "♠"), "♠"))="♠", 'Program 1'!A1:A, );
 IF(IFERROR(REGEXEXTRACT(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE('Program 2'!A1:Z), , 999^99))), "Pass", "♠"), "♠"))="♠", 'Program 2'!A1:A, )},
 "where Col1 is not null order by Col1", 0)))}

0

if you want to VLOOKUP it:

=ARRAYFORMULA(IF(LEN(A2:A), IF(IFERROR(VLOOKUP(A2:A, UNIQUE(QUERY({
 IF(IFERROR(REGEXEXTRACT(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE('Program 1'!A1:Z), , 999^99))), "Pass", "♠"), "♠"))="♠", 'Program 1'!A1:A, );
 IF(IFERROR(REGEXEXTRACT(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(
 TRANSPOSE('Program 2'!A1:Z), , 999^99))), "Pass", "♠"), "♠"))="♠", 'Program 2'!A1:A, )},
 "where Col1 is not null", 0)), 1, 0))<>"", "PASS", "FAIL"), ))

0