I’m creating a range of data using the following formula, and it works just fine without the FILTER part:
=FILTER(WRAPROWS(TRANSPOSE(FLATTEN(CHOOSECOLS('master sheet'!A2:AT, 1, 2, 5, 7, 8, 1, 2, 5, 9, 10, 1, 2, 5, 11, 12, 1, 2, 5, 13, 14))), 5, ""), D2:D<>"", E2:E<>"")
The trouble I’m pulling in my data from another sheet using ‘master sheet’!A2:AT, so I don’t know how many rows is in that dataset. So, when I try to create filter criteria D2:D<>””, E2:E<>””, I get a mismatched range error.
How can I set up filter criteria without knowing the height of the range I’m creating here? Or alternatively, how can I know the height of my range and scale my filter criteria with it?
1
Use LET to first store your initial array in a variable. This will allow you the opportunity to count its rows and constrain columns D and E before applying the FILTER.
This approach assumes there are an equal number or greater of rows in columns D and E as in 'master sheet'
=LET(array,WRAPROWS(TRANSPOSE(FLATTEN(
CHOOSECOLS('master sheet'!A2:AT,
1, 2, 5, 7, 8, 1, 2, 5, 9, 10, 1,
2, 5, 11, 12, 1, 2, 5, 13, 14))),5),
seq,SEQUENCE(1,ROWS(array)),
FILTER(array, CHOOSEROWS(D2:D,seq)<>"",
CHOOSEROWS(E2:E,seq)<>"")))
If you instead want to limit both sheets to the lowest common number of rows then you can constrain the arrays based on the MIN of both heights.
=LET(rng,'master sheet'!A2:AT, d,D2:D, e,E2:E,
arr,LAMBDA(x, ARRAY_CONSTRAIN(x,
MIN(ROWS(rng), ROWS(d)), COLUMNS(x))),
FILTER(WRAPROWS(TRANSPOSE(FLATTEN(
CHOOSECOLS(arr(rng),
1, 2, 5, 7, 8, 1, 2, 5, 9, 10, 1,
2, 5, 11, 12, 1, 2, 5, 13, 14))),5), arr(d)<>"", arr(e)<>""))
0