Concatenating Google sheet ranges conditionally

Question

I am currently attempting to union multiple ranges, but each range has to have a condition met first.

My first attempt at this was the following formula:

={ IF(condition_one, range_one, IFERROR(0/0)); IF(condition_two, range_two, IFERROR(0/0)); IF(condition_three, range_three, IFERROR(0/0)) }

This works only if all three conditions are true. If any of them are false, I get an error:

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

Essentially, range_one, range_two, and range_three are all pre-filtered ranges of the same range. They're all unique from one another. I want to be able to filter the original range by multiple conditions with each condition returning true concatenating onto the result.

My second attempt at this was to filter the whole range with the conditions that the row existed within the pre-filtered range:

=FILTER(whole_range, IF(condition_one, COUNTIF(range_one, INDEX(whole_range,, 1)), FALSE))

This worked for a single range if the condition was true, otherwise it returned #N/A (which is fine, I can handle that as it shouldn't be returning any rows at this point). When I added the additional conditionals however, I got two different errors under different conditions.

If anything less than all the conditions were true (including if all were false), I'd get this error:

FILTER has mismatched range sizes. Expected row count: 79, column count: 1. Actual row count: 1, column count: 1.

If all the conditions were true, this error:

No matches are found in FILTER evaluation.

Now I'm probably overthinking this. Unfortunately that's just the way my brain functions and I tend to miss obvious and/or simple solutions and I get the feeling that's what's happening here.

Is anyone able to assist me in this matter?

As per request, I have created an example sheet:
https://docs.google.com/spreadsheets/d/12RGLRyljFCmDgL9mhMAo9LvpGvltCJtIB69WI0uKYNY/edit?usp=sharing

Answer 1

Use this pattern:

=arrayformula( 
  query( 
    { 
      if(C3, TABLE_ROWS_BY_RARITY(B3), iferror(TABLE_ROWS_BY_RARITY(B3) / 0)); 
      if(C4, TABLE_ROWS_BY_RARITY(B4), iferror(TABLE_ROWS_BY_RARITY(B4) / 0)); 
      if(C5, TABLE_ROWS_BY_RARITY(B5), iferror(TABLE_ROWS_BY_RARITY(B5) / 0)) 
    }, 
    "where Col1 is not null", 0 
  ) 
)

Answer 2

One option is if the each condition applies for the whole "subrange" you could do something like this:

={IFERROR(FILTER(range1,condition1),"");IFERROR(FILTER(range2,condition2),"");IFERROR(FILTER(range3,condition3),"")}

Yes, it will leave an empty row when a subrange doesn't meet the condition but perhaps it works for you. Or you could do something like this:

=LAMBDA(filteredranges,FILTER(filteredranges,filteredranges <>""))({IFERROR(FILTER(range1,condition1),"");IFERROR(FILTER(range2,condition2),"");IFERROR(FILTER(range3,condition3),"")})

Let me know if this helps!

NOTE: I'm thinking now that probably your ranges do have more than one column, then in my formula (or yours) you could change the second parameter of IFERROR with this:

IFERROR(-put any formula here-,SPLIT(" , , ",","))

And put as many commas inside SPLIT as columns you have in your ranges -1 (be aware that all your ranges should have the same amount of columns)

OPTION 2:

More according to your second option, you can apply FILTER also concatenated; but be aware that the conditions of the FILTER should be a column of n number of rows (your amount, 79 for example). So you should be careful about how you express your conditions: if you want to apply to the first column of any of the ranges use, for example: INDEX(range1,,1)<>"" or REGEXMATCH(INDEX(range1,,1),"word") or things like that. If you accomplish to generate a column of the exact same amount of rows by concatenating the conditions as in the next example formula, your whole range will be well "selected" and there will be no empty rows. Let me know if you have doubts!

=LAMBDA(range1,range2,range3,LAMBDA(joinedrange,FILTER(joinedrange,{condition1onrange1;condition2onrange2;condition3onrange3}))({range1;range2;range3}))(A:B,C:D,E:F)