How to use FILTER() to eliminate columns AND rows by criteria?

[TheMacroNoob]

I don't have access to VSTACK yet, so I am using a workaround to stack many dynamic arrays on top of each other in a list.

To do this, my helper array formula is as follows:

=LET(NUMCols,COLUMNS(Sheet1!C6:E15),RowsA,ROWS(Sheet1!C6:E15),RowsB,ROWS(Sheet2!C6:E15),SeqCol,SEQUENCE(RowsA+RowsB),SeqRow,SEQUENCE(1,NUMCols),Result,IF(SeqCol<=RowsA,INDEX(Sheet1!C6:E15,SeqCol,SeqRow),INDEX(Sheet2!C6:E15,SeqCol-RowsA,SeqRow)),Result)

This results in the following:

EMT1529.xlsx
	H	I	J
4		Helper
5	Building	Damaged	Deductible
6	1	Yes	72762
7	2	Yes	61570
8	3	Yes	61849
9	4	Yes	71001
10	5	Yes	71044
11	6	Yes	96660
12	7	Yes	110792
13	8	Yes	75367
14	9	Yes	59733
15	10	Yes	78861
16	A	Yes	107967
17	B	0	112415
18	C	0	118634
19	D	0	76366
20	E	Yes	111093
21	F	0	103157
22	G	Yes	64347
23	H	Yes	60256
24	I	0	62276
25	J	Yes	94330
Sheet3

Cell Formulas
Range	Formula
H6:J25	H6	=LET(NUMCols,COLUMNS(Sheet1!C6:E15),RowsA,ROWS(Sheet1!C6:E15),RowsB,ROWS(Sheet2!C6:E15),SeqCol,SEQUENCE(RowsA+RowsB),SeqRow,SEQUENCE(1,NUMCols),Result,IF(SeqCol<=RowsA,INDEX(Sheet1!C6:E15,SeqCol,SeqRow),INDEX(Sheet2!C6:E15,SeqCol-RowsA,SeqRow)),Result)
Dynamic array formulas.

For starters, is there a way to wrap the above formula in a FILTER function to filter out the "Damaged" column, and any row that doesn't contain "Yes"?

As of now I am, in another column, using this formula to filter: =FILTER(H6#,(H5:J5="Building")+(H5:J5="Deductible")) to narrow down my columns. Can I add an argument to eliminate the rows containing "Yes" in the damaged column?

 

[Fluff]

How about

=LET(NUMCols,COLUMNS(Sheet1!C6:E15),RowsA,ROWS(Sheet1!C6:E15),RowsB,ROWS(Sheet2!C6:E15),SeqCol,SEQUENCE(RowsA+RowsB),SeqRow,SEQUENCE(1,NUMCols),Result,IF(SeqCol<=RowsA,INDEX(Sheet1!C6:E15,SeqCol,SeqRow),INDEX(Sheet2!C6:E15,SeqCol-RowsA,SeqRow)),FILTER(INDEX(Result,SeqCol,{1,3}),INDEX(Result,,2)="Yes"))

 

[TheMacroNoob]

How about

=LET(NUMCols,COLUMNS(Sheet1!C6:E15),RowsA,ROWS(Sheet1!C6:E15),RowsB,ROWS(Sheet2!C6:E15),SeqCol,SEQUENCE(RowsA+RowsB),SeqRow,SEQUENCE(1,NUMCols),Result,IF(SeqCol<=RowsA,INDEX(Sheet1!C6:E15,SeqCol,SeqRow),INDEX(Sheet2!C6:E15,SeqCol-RowsA,SeqRow)),FILTER(INDEX(Result,SeqCol,{1,3}),INDEX(Result,,2)="Yes"))

Thank you, this is more than I could have hoped for.

Is there a quick way to add more sheets with the same cell ranges to this formula? I thought I could just name RowsC, add it to the SeqCol, and add another index, but I was seemingly mistaken?

 

[Fluff]

That should work, but you will need to make the IF a nested IF to account for the extra sheet.
Also do you have the VSTACK function yet?

 

[TheMacroNoob]

That should work, but you will need to make the IF a nested IF to account for the extra sheet.
Also do you have the VSTACK function yet?

Hmm, I tried to make a nested IF alongside adding a new sheet, and adjusting SeqCol, but I am getting an error.

=LET(NUMCols,COLUMNS(Sheet1!C6:E15),RowsA,ROWS(Sheet1!C6:E15),RowsB,ROWS(Sheet2!C6:E15),RowsC,ROWS(Sheet3!C6:E15),SeqCol,SEQUENCE(SUM(RowsA,RowsB,RowsC)),SeqRow,SEQUENCE(1,NUMCols),Result,IF(SeqCol<=RowsA,INDEX(Sheet1!C6:E15,SeqCol,SeqRow),IF(SeqCol<=RowsA,INDEX(Sheet2!C6:E15,SeqCol-RowsA,SeqRow),INDEX(Sheet2!C6:E15,SeqCol-RowsA,SeqRow))),FILTER(INDEX(Result,SeqCol,{1,3}),INDEX(Result,,2)="Yes"))

I would love to use VSTACK, but as of now I am not an Office Insider and my employer wouldn't sign me up. I can't wait for the new DAFs to be implemented in 365 for everyone.

 

[TheMacroNoob]

I think my main confusion with this formula is with what the IF statement is actually doing and how to add more criteria for further nested IFS. This is hard to wrap my head around at the moment.

 

[Fluff]

Ok, how about

=LET(NUMCols,COLUMNS(Sheet1!C6:E15),RowsA,ROWS(Sheet1!C6:E15),RowsB,RowsA+ROWS(Sheet2!C6:E15),RowsC,RowsB+ROWS(Sheet3!C6:E15),SeqRows,SEQUENCE(RowsC+ROWS(Sheet4!C6:E15)),SeqCols,SEQUENCE(1,NUMCols),Result,IF(SeqRows<=RowsA,Sheet1!C6:E15,IF(SeqRows<=RowsB,INDEX(Sheet2!C6:E15,SeqRows-RowsA,SeqCols),IF(SeqRows<=RowsC,INDEX(Sheet3!C6:E15,SeqRows-RowsB,SeqCols),INDEX(Sheet4!C6:E15,SeqRows-RowsC,SeqCols)))),FILTER(INDEX(Result,SeqRows,{1,3}),INDEX(Result,,2)="Yes"))

 

[TheMacroNoob]

Ok, how about

=LET(NUMCols,COLUMNS(Sheet1!C6:E15),RowsA,ROWS(Sheet1!C6:E15),RowsB,RowsA+ROWS(Sheet2!C6:E15),RowsC,RowsB+ROWS(Sheet3!C6:E15),SeqRows,SEQUENCE(RowsC+ROWS(Sheet4!C6:E15)),SeqCols,SEQUENCE(1,NUMCols),Result,IF(SeqRows<=RowsA,Sheet1!C6:E15,IF(SeqRows<=RowsB,INDEX(Sheet2!C6:E15,SeqRows-RowsA,SeqCols),IF(SeqRows<=RowsC,INDEX(Sheet3!C6:E15,SeqRows-RowsB,SeqCols),INDEX(Sheet4!C6:E15,SeqRows-RowsC,SeqCols)))),FILTER(INDEX(Result,SeqRows,{1,3}),INDEX(Result,,2)="Yes"))

I ALMOST understand... Until next time.

Now to add another 20 sheets to the formula for my actual data set..

Thank you so much for your help,

 

[Fluff]

Glad to help & thanks for the feedback.