Formula to Dynamically Stack Columns with Spill Down

Question

For EXCEL 365

I am looking for a formula that will dynamically stack the contents of two columns, one on top of the other. For example, if I start with:

I want the formula to produce:

I can achieve this with "semi-dynamic":

=IF(ROWS($1:1)>COUNTA(A:A)+COUNTA(C:C),"",IF(ROWS($1:1)<COUNTA(A:A)+1,INDEX(A:A,ROWS($1:1)),INDEX(C:C,ROWS($1:1)-COUNTA(A:A))))

It just INDEXs down the first column until all items are retrieved and then INDEXs down the second column.

it is dynamic in the sense that the output adjusts to adding/removing items from columns A or B
it is manual in the sense that it must be manually copied downward rather than spill down by itself

I can get the desired behavior with VBA:

Option Explicit

Public Function stack2(r1 As Range, r2 As Range) As Variant
    Dim c1 As Long, c2 As Long, r As Range, temp
    Dim i As Long
    
    c1 = r1.Count
    c2 = r2.Count
    ReDim temp(1 To c1 + c2, 1 To 1)
    i = 1
    
    For Each r In r1
        temp(i, 1) = r.Value
        i = i + 1
    Next r
    For Each r In r2
        temp(i, 1) = r.Value
        i = i + 1
    Next r
    
    stack2 = temp
End Function

But I need a formula rather than VBA. I can retrieve and spill-down a single column with:

=INDEX(A:A,SEQUENCE(COUNTA(A:A)))

But I can't figure out how to handle two columns.

Any ideas??

@ScottCraner Order is not important.... **A1,C1,A2,C2** is Okay — Gary's Student, Aug 20 '20 at 14:28
is B to be skipped on purpose or will it be blank or does it get included if there is data? — Scott Craner, Aug 20 '20 at 14:33
see if any of these answer your problem: https://stackoverflow.com/questions/62204826/excel-unique-across-columns/62903013#62903013 — Scott Craner, Aug 20 '20 at 14:34
Both answers work just fine! I just discard the `SORT()` and `UNIQUE()` it handles leaving column **B** blank as well! — Gary's Student, Aug 20 '20 at 15:10

score 1 · Accepted Answer · answered Aug 20 '20 at 15:10

1

replace the ROWS($1:1) with SEQUENCE(COUNTA(A:A)+COUNTA(C:C))

=IF(SEQUENCE(COUNTA(A:A)+COUNTA(C:C))<COUNTA(A:A)+1,INDEX(A:A,SEQUENCE(COUNTA(A:A)+COUNTA(C:C))),INDEX(C:C,SEQUENCE(COUNTA(A:A)+COUNTA(C:C))-COUNTA(A:A)))

answered Aug 20 '20 at 15:10

Scott Craner

148,073
10
49
81

Thank you very much! – Gary's Student Aug 20 '20 at 15:13

Formula to Dynamically Stack Columns with Spill Down

1 Answers1