0

Simple question, but I can't figure it out.

enter image description here

In the spreadsheet above I have a table in A1:B5. I have a filter in A7 and its value is an array.

In A11, I want to textjoin all the values of the filter results (so it should be "A,C,E"), but I can't figure out how to do it. It seems like the reference to A7 just returns the first value. How do I reference the whole array?

1
  • 1
    Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a blank sheet maker that lets you share safely. Mar 20 at 14:16

2 Answers 2

0

No Spilled Range Operator

Sheets does not have the spilled range operator # like Excel does.

In Sheets, you can't use A7 (A7#) but need to specify the entire spilled range A7:A9.

So your formula in Sheets would be:

=TEXTJOIN(", ", TRUE, A7:A9)

Or the equivalent:

=TEXTJOIN(", ", 1, A7:A9)

Unknown Spilled Range Size

It is common to not know in advance the range dimensions required to accommodate the results of a formula, and also for those requirements to change dynamically as the source data changes.

This issue is often addressed using oversized ranges. For example, in your question the TEXTJOIN formula needed a range of 3 rows in order to capture the spilled results. If there might be as many as 10 results, the formula would be adjusted to:

=TEXTJOIN(", ", 1, A7:A16)

Since TEXTJOIN's ignore_blanks argument is set to TRUE (1), any number of results less than or equal to 10 would be accommodated.

You can even use unbounded (or infinite) ranges. These ranges will continue to grow as the new rows/columns are added.

For example:

=TEXTJOIN(", ", 1, A7:A)

Lastly, as shown in Rubén's answer, the original formula that is creating the spill range can be incorporated into your formula, then there is no need to predict the dinensions or ignore blanks:

=JOIN(", ",
  FILTER(A1:A5, B1:B5=1))

JOIN now replaces TEXTJOIN making the formula shorter since there's no risk of introducing blank values.

If there's a risk that FILTER returns no matches the #N/A! error can be handled with IFNA:

=IFNA(JOIN(", ",
  FILTER(A1:A5, B1:B5=1)))

Finally, if you move your results to column C you could make your source formula oversized or unbounded so it grows as you add new data:

# Oversized
=IFNA(JOIN(", ",
  FILTER(A1:A500, B1:B500=1)))

# Unbounded
=IFNA(JOIN(", ",
  FILTER(A1:A, B1:B=1)))
2
  • This would be fine if I knew how big the array A7 was. But since it depends on the filter, I don't.
    – Hugh Beyer
    Mar 20 at 14:05
  • Actually, since I have to allow for A7 to be of indefinite size, I can't put anything below it--so I can use sheets' open-ended range, e.g. A7:A.
    – Hugh Beyer
    Mar 20 at 14:25
0

The TEXTJOIN third argument could be a range or subformula, i.e., a FILTER function including other functions.

Example

=TEXTJOIN(",",true,FILTER(A1:A3,NOT(ISBLANK(B1:B3))))

There might be other cases where ARRAYFORMULA might be needed, i.e., when using IF, VLOOKUP or other functions instead of FILTER.

Example

=ArrayFormula(TEXTJOIN(",",true,IF(ISBLANK(B1:B3),,A1:A3)))

Tip: Open references, named ranges and named formulas might be handy in real cases.


Open References

Open references in FILTER and similar functions when the range to be filtered might grow over time, but this might not be a good option if you export your spreadsheet and on some edge cases. In the formulas above, the ranges used were A1:A3 and B1:B3, the open references for these formulas could be A:A and B:B, respectively.

Named Ranges

Named ranges might be helpful as they might autocomplete, and if you have many cells making use of the same range, it might help to make the formulas clearer and to reduce the maintenance load, i.e., when having to update the range.

Named Formulas

Named formulas are part of a recent enhancement to Google Sheets. They can help to make it easier to handle and maintain your spreadsheet.

8
  • An arrayformula() wrapper is not needed here, because textjoin() and filter() are both capable of processing arrays without it. Not sure how named ranges and named formulas would be relevant to the question. Mar 19 at 18:32
  • @doubleunary Thanks for the feedback. Mar 19 at 19:37
  • 1
    Ugh. So all my calculations have to be rolled up to the textjoin WHICH means if I have several cells depending on the filter result in A7, that filter has to be repeated in all of them. Which is what I was hoping to avoid. (I would use named ranges in the real SS, this is just to show the issue.)
    – Hugh Beyer
    Mar 20 at 14:08
  • 1
    The two current answers both address the question quite fine. It is the question that requires attention. Please edit the question and add all relevant detail there. Mar 20 at 14:16
  • 1
    Yeah, as you said both answers address the question. I "accepted" the one I'll use in my case.
    – Hugh Beyer
    Mar 20 at 15:30

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.