Is it possible to combine spillover arrays in a formula?

Question

Level 1

4 points

Is it possible to combine spillover arrays in a formula?

Spillover arrays allow you to populate an entire column (or row) using a single formula at the start of the array. For example, suppose you have a table with the following

      A        B                     C                      D
1   alpha    delta        =VSTACK(A1:A3,B1:B3)   =FILTER(C1#,C1#<>"epsilon")
2   beta    epsilon
3   gamma    zeta

The formula shown in C1, when run, would produce an array that spills over onto rows 2-6 listing the six greek characters. But, suppose you had filtering requirement so that you wanted to eliminate "epsilon". The formula in D1, when run, grabs the spill-over from the formula in C1 (using a "spill range operator", #) and produces an array that lists just five entries.

      A        B         C        D
1   alpha    delta     alpha    alpha      
2   beta    epsilon     beta     beta
3   gamma     zeta     gamma    gamma
4                      delta    delta
5                     epsilon    zeta
6                      zeta

My question is, can you nest the spillover array produced by the VSTACK function directly inside the FILTER? It isn't obvious how you would format the filtering criteria. To illustrate, it would be nice to have some sort of "<VSTACK-spillover-operator>" so that I could code:

=FILTER( VSTACK(A1:A3,B1:B3), <VSTACK-spillover-operator> <> "epsilon")

Thanks!

iPad Air 3

Posted on May 16, 2025 1:01 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,348 points

May 16, 2025 8:37 PM in response to AppMe!

Is this what you mean?

=FILTER(VSTACK(A2:A4,B2:B4),VSTACK(A2:A4,B2:B4)≠"epsilon")

Reply

Answer 2

SGIII

Level 8

37,532 points

May 17, 2025 7:46 AM in response to AppMe!

Lots of ways to do this. You can combine dynamic array formulas in all sorts of ways.

Instead of VSTACK you can use TOCOL with the 'ignore' parameter set to 1 ('ignore blanks'). This allows you, in typical Numbers style, to reference entire columns rather than make Excel-like references to ranges within columns:

=FILTER(TOCOL(A:B,1),TOCOL(A:B,1)<>"epsilon")

TOCOL - Apple Support

A variation using LET:

=LET(greeks,TOCOL(A:B,1),FILTER(greeks,greeks<>"epsilon"))

With LAMBDA:

=LAMBDA.APPLY(TOCOL(A:B,1),LAMBDA(Greek,FILTER(Greek,Greek<>"epsilon")))

And sorted:

=SORT(FILTER(TOCOL(A:B,1),TOCOL(A:B,1)<>"epsilon"))

You can also throw UNIQUE in there when needed.

Definitely cool.

SG

Reply

Answer 3

AppMe! Author

Level 1

4 points

May 17, 2025 5:29 AM in response to Badunit

Thank you, Badunit!

That works perfectly. Plus, it gives me a better understanding of why we need the LAMBDA function. It is wordy compared to your solution, but this also works:

LAMBDA.APPLY(VSTACK(A1:A3,B1:B3),LAMBDA(Greek,FILTER(Greek,Greek≠"Epsilon")))

Here, “Greek” is a formula token representing the VSTACK function. That permits you to spell out the function once and use the token as often as is needed. Kinda cool.

Reply

Answer 4

AppMe! Author

Level 1

4 points

May 17, 2025 8:50 AM in response to SGIII

Thank you, SGIII,

I’ve put the “TOCOL” function straight to work! As you seemed to have guessed, a big part of the motivation for this question was simply to get rid of blanks. The TOCOL is great for that. Powerful tool, especially when combined with pivot tables.

Reply