In Excel, can I "splice" multiple Dynamic/Spilled Arrays into a single Dynamic/Spilled Array?

Question

IN A NUTSHELL

I have four (say) columns of data, each a Dynamic/Spilled Array. I'd like to "stack" them into another column so as to produce a single Dynamic/Spilled Array comprising the "splice" of the four originals.

I want to do it without VBA, and such that if the sizes of any of the four initial arrays change, it requires minimal, and ideally no change to the formulae etc being used to do the splicing.

TL;DR DETAILS

I already have a method for doing it involving the creation of a "helper" column, but it's not ideal. It works like this (example here):

1. First, I choose a maximum "dimension" for the original arrays; i.e. the largest size that the largest of the four will ever need to be.
2. I then create, as a helper column, a "manual" stacking of the four original arrays. But I do that such that they now each take up not their original sizes, but that maximum dimension I chose. (So in my typical use case at least, where the four originals are often of very different sizes, there will be a lot of gaps in the helper column.)
3. Finally I prune out the gaps using FILTER(). That not only prunes out the gaps but also converts the whole sorry mess into a nice, contiguous range, in the form of a Dynamic/Spilled array. Which is what I want.

That works but it has (at least) two obvious limitations:

1. The need for that hard-coded maximum dimension. It fails my requirement of not having to change formulae if the initial array sizes change. Yes, my use of a max dimension gives me some immunity to that kind of resize-induced change, but only if the sizes stay WITHIN that overall maximum. I can of course increase the chances of staying within that limit by increasing the limit itself, but that has a cost in size and speed. See next point.
2. The fact that a) my four initial arrays can differ widely in size, and b) for any given use case, I may only want to look at some small slice of the entire set of data, this method is very space-wasteful. The helper column can be very, very large, despite the fact that in the end I only want to look at that small slice. It's clumsy and, more important, slow.

What I'd like is, given four initial arrays rooted at C8, D8, E8, and F8 respectively (as per my attached example), to be able to shove something like this into a single cell:

=(C8#,D8#,E8#,F8#)

and have it all Just Work.

Any ideas?

P.S. I've provided my example using a link to an Excel file in DropBox. But if folk are like me, they'll not be keen on clicking random links from random dude on the internet. I know I wouldn't! (That said, this would be a helluva long-winded way of trying to drop some malware on y'all, so there's always that I suppose. :-) ) So if there's a better way to attach-or-otherwise-provide such files, I'd appreciate hearing of them.

Answer 1

Try:

B1: =FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE($C$8:$F$17)),",","</s><s>")&"</s></t>","//s")

• TEXTJOIN creates a comma delimited list of non-blank entries within the range.
• We TRANSPOSE the range first so that the ordering is by Columns and not by Rows (which is the usual case with this type of range reference.
• Construct an XML where the nodes are separated by the commas.
• FILTERXML then splits each node into a separate array element, which, with the Dynamic Array feature, will SPILL down the column

Note: In the formula, I used a hard-coded range reference. However, depending on information I don't have, one could use a dynamic range reference. But that depends on how the range might be changing.

The simplest method would be to make this a Table. In that case, the reference would automatically expand/contract as you added/deleted/inserted rows/columns

Tables don't play nicely with spilled arrays.

• So I changed the formula in the first cell of your example table to:

    C12: =IFERROR(INDEX(SEQUENCE(C$6,1,IF(COLUMNS($A:A)=1,1,COLUMNS($A:A)*6),1),ROWS($1:1)),"")

And I also used Table1 as the range reference in the same formula as above.

and, of course, the formula would Fill Down automagically.

On your example sheet:

If you really want to use the dynamic spilled arrays (which precludes using a Table), you can, on your example worksheet, define dynamically the array something like:

=$C$11:INDEX($A:$XFD,MAX($6:$6)+10,COUNT($6:$6)+2)

and the formula would be:

=FILTERXML("<t><s>" & SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE($C$11:INDEX($1:$1048576,MAX($6:$6)+10,COUNT($6:$6)+2))),",","</s><s>")&"</s></t>","//s")

I note Excel changed A:XFD to 1:1048576 but they refer to the same area (the entire sheet).

Answer 2

I understand that you want to avoid VBA. However, if you re-consider:

If you use a VBA User Defined Function, you can stack dynamic arrays in the same way you would stack standard array formulas.

Say we start with data in columns A and B and we want to stack the unique items in column B directly under the unique items in column A:

First enter this UDF in a standard module:

Public Function stack(arr1 As Variant, arr2 As Variant)
    Dim i As Long, a

    ReDim temp2(1 To UBound(arr1) + UBound(arr2))
    i = 1
    For Each a In arr1
        temp2(i) = a
        i = i + 1
    Next a

    For Each a In arr2
        temp2(i) = a
        i = i + 1
    Next a
    stack = Application.Transpose(temp2)

End Function

Then in C1 enter:

=stack(UNIQUE(A1:A7),UNIQUE(B1:B8))

NOTE:

1. The C1 formula will spill-down automatically, so it does not need to be array-entered.
2. for multi-column spill-downs, the details would be a lot more complicated, but the approach would be the same.