Excel - How to filter a table on multiple columns (OR relation) and change values based on other columns

Question

here's what I have in an Excel table (Table1):

This table contains period columns containing amounts for the entire year (P1_val, ... P12_val) and additionally 12 other columns containing a Yes/No value based on some formula (P1_YN, ... P12_YN). To explain my challenge I just use 2 columns. What I want to achieve:

• apply a condition to the P1_val through P12_val columns to display 0 when their respective Px_YN column is 'No' (as they will also get displayed and pollute my totals)
• filter on all P1_YN through P12_YN columns using OR where value is 'Yes'

The outcome should be a filtered table where only rows appear where any of the _YN columns is 'Yes'; amounts are displayed as is when the corresponding Px_YN is 'Yes', i.e. if P1_YN = Yes then value is shown but if P2_YN = No then amount must be 0 etc. The row itself is shown as at least 1 period is marked as Yes.

I have been trying to get this to work using the new FILTER() function but that will only resolve my first challenge (i.e. filter the table).

To resolve the other challenge, I have been looking at arrays but have not come across a solution that works on multiple columns, just this one:

=IF(ISNUMBER(MATCH(Table1[Period_YN];{"Yes"};0));Table1[Period_1_val];"0")

But that only shows 1 column, where I need the full table to be displayed so I can apply the FILTER(), or the other way around (first change value, then filter).

Note: I cannot change the original table as amounts are needed for other reports.

I hope I explained it well.

Any ideas would be much appreciated.

Answer 1

This formula will adapt to any number of column pairs after the first column:
Note that it may or may not return expected results if you have an unmatched val column as you show in your example

=LET(
    numCols, (COLUMNS(Table1) - 1) / 2,
    yn, SEQUENCE(, numCols, 2),
    val, SEQUENCE(, numCols, 2 + numCols),
    f, FILTER(Table1, BYROW(CHOOSECOLS(Table1, yn) = "Yes", LAMBDA(arr, OR(arr)))),
    z, MAP(CHOOSECOLS(f, yn), CHOOSECOLS(f, val), LAMBDA(a, b, IF(a = "No", 0, b))),
    HSTACK(CHOOSECOLS(f, 1, yn), z)
)

Answer 2

Try this formula:

=LET(data,Table1,cntPeriods,2,
periodYN,CHOOSECOLS(data,SEQUENCE(,cntPeriods,2)),
yesOK,MAKEARRAY(ROWS(data),1,LAMBDA(r,c,OR(INDEX(periodYN,r)="yes"))),
periodValues,CHOOSECOLS(data,SEQUENCE(,cntPeriods,cntPeriods + 2)),
periodValuesX,periodValues * --(periodYN="yes"),
FILTER(HSTACK(CHOOSECOLS(data,1),periodYN,periodValuesX),yesOK))

You have to adjust cntPeriods to 12.

yesOK creates an array which returns true if at least one Period is set to yes --> this is then used in the final filter.

periodValuesX sets the value to 0 in case periodYN-value is no as --(periodYN="yes") returns 0, if yes check will return 1.