2

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

4
  • Content In These Cells Doesn't Matter — you have gone to great lengths to abstract the question and make the data symbolic. That makes it harder, not easier, to answer your question. Consider showing realistic-looking sample data and your manually entered desired results from that sample data for one or more use cases. Dec 9, 2022 at 6:43
  • @doubleunary I don't understand how it matters in this case? I need to return the entire table regardless of the content. They could be all blank, all have the same content, all be different. It doesn't matter, I'd still need it. To be clear, I wasn't obfuscating it out of malice or anything, I just didn't see it as relevant.
    – Spedwards
    Dec 9, 2022 at 6:47
  • The type of data in the table matters. For example, the query() function will only accept one data type per column, so if your data columns contain a mix of text and numbers, or the occasional date or Boolean, the majority type will rule, and other types will be returned as nulls. Your data is sparse, so the column type may even end up as being null. There are many other similar considerations that may or may not prohibit the use of split(), join(), match() and so on. Dec 9, 2022 at 6:55
  • @doubleunary Ahh, I was admittedly unaware of the issue with QUERY(). To be clear, the data in those cells will always be strings. There may also be empty cells.
    – Spedwards
    Dec 9, 2022 at 6:56

2 Answers 2

2

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 
  ) 
)
2
  • This doesn't appear to work. I've added it to the example sheet so you can see for yourself. I'm also not sure if it's an error but you have NOT NOT NULL in your query, so I tried IS NULL, IS NOT NULL, and IS NOT NOT NULL just in case. If I have made a typo, let me know.
    – Spedwards
    Dec 9, 2022 at 6:55
  • I did make a typo. I included a semi-colon at the end of the third item. This does work. Thank you :)
    – Spedwards
    Dec 9, 2022 at 6:59
1

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)
3
  • A few things: Your first snippet returns nothing. No matter what the conditions are, there's just a blank cell. The second snippet throws an error, No matches are found in FILTER evaluation.. My range does have 13 columns, however I don't understand your comment about SPLIT(). Lastly your last snippet returns the same error returns the same error as my second snippet except Actual row count: 1 (I added a range to test, otherwise it was 3)
    – Spedwards
    Dec 9, 2022 at 4:48
  • Could you share a small sample of what the conditions are or a sample spreadsheet?, As far as I have tested it works just fine, probably it's just a little adaptation for your particular case. Given how abstractly we're talking probably there are things mis-comunicated
    – Martín
    Dec 9, 2022 at 5:09
  • You're not wrong. I've edited my main post to include a link to an example sheet at the bottom.
    – Spedwards
    Dec 9, 2022 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.