Merge / stack excel columns range definition without VBA

Question

I want to combine / stack the values of 2 different columns and get the unique values. If the range is adjacent, it works fine. For example:

=UNIQUE(FILTERXML(""&SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE(SRC!$A$1:$C$22)),",","")&"","//b"))

I don't know, however, how I can do this with non adjacent columns for example column A and C. Defining the area in transpose like this A:A,C:C does not work. So basically, I have two questions:

How can I stack / merge non adjacent columns (I assume there a multiple ways) ?
How can I define an irregular range in a formula like (A1:A12,C2:C22)?

I need to use formulas, not VBA or the Excel GUI. Thx!

user_9758194 · Answer

Since short (currently in ms365's BETA-channels), there is the option to VSTACK() different ranges into a single column. The parameters can be non-contiguous irregular (but vertical) arrays:

Formula in E1:

=UNIQUE(VSTACK(A2:A5,C3:C7))

Do note, that even if you have irregular non-contiguous ranges, TEXTJOIN() can easily hold multiple of those instead of just a single range. Apply that logic to the sample data above:

=UNIQUE(FILTERXML(""&TEXTJOIN("",,A2:A5,C3:C7)&"","//s"))

Sidenote; but related questions could be found here and here for more inspiration.

user_6402040 · Answer

If you do not have access to VSTACK() and you have ranges one or more columns in width of variable heights and locations, here is another possible solution. This allows up to 10 separate ranges.

Place this formula in the cell where you want the combined arrays.

=Array_Stacker(False,False,A1:B20,D12:E18,G5:H10)

Name this LAMBDA function "Array_Stacker" in Names Manager.

=LAMBDA(unique_TRUE_or_FALSE, filter_blanks_TRUE_or_FALSE, range1, range2, [range3], [range4], [range5], [range6], [range7], [range8], [range9], [range10],
        LET(
            unique, IF(ISOMITTED(unique_TRUE_or_FALSE), FALSE, unique_TRUE_or_FALSE),
            blanks, IF(ISOMITTED(filter_blanks_TRUE_or_FALSE), FALSE, filter_blanks_TRUE_or_FALSE),
            numcols, COLUMNS(range1),
            height01, ROWS(range1),
            height02, ROWS(range2),
            height03, IF(ISOMITTED(range3), 0, ROWS(range3)),
            height04, IF(ISOMITTED(range4), 0, ROWS(range4)),
            height05, IF(ISOMITTED(range5), 0, ROWS(range5)),
            height06, IF(ISOMITTED(range6), 0, ROWS(range6)),
            height07, IF(ISOMITTED(range7), 0, ROWS(range7)),
            height08, IF(ISOMITTED(range8), 0, ROWS(range8)),
            height09, IF(ISOMITTED(range9), 0, ROWS(range9)),
            height10, IF(ISOMITTED(range10), 0, ROWS(range10)),
            heightAll, SUM(
                height01,
                height02,
                height03,
                height04,
                height05,
                height06,
                height07,
                height08,
                height09,
                height10
            ),
            x, IFS(
                ISOMITTED(range3),
                1,
                ISOMITTED(range4),
                2,
                ISOMITTED(range5),
                3,
                ISOMITTED(range6),
                4,
                ISOMITTED(range7),
                5,
                ISOMITTED(range8),
                6,
                ISOMITTED(range9),
                7,
                ISOMITTED(range10),
                8,
                NOT(ISOMITTED(range10)),
                9
            ),
            ranges, CHOOSE(
                x,
                (range1 , range2),
                (range1 , range2 , range3),
                (range1 , range2 , range3 , range4),
                (range1 , range2 , range3 , range4 , range5),
                (range1 , range2 , range3 , range4 , range5 , range6),
                (range1 , range2 , range3 , range4 , range5 , range6 , range7),
                (range1 , range2 , range3 , range4 , range5 , range6 , range7 , range8),
                (range1 , range2 , range3 , range4 , range5 , range6 , range7 , range8 , range9),
                (range1 , range2 , range3 , range4 , range5 , range6 , range7 , range8 , range9 ,
                    range10)
            ),
            formula01, SEQUENCE(heightAll),
            formula02, SEQUENCE(heightAll, numcols),
            formula03, IFS(
                formula01 <= height01,
                formula01,
                formula01 <= height01 + height02,
                formula01 - height01,
                formula01 <= height01 + height02 + height03,
                formula01 - height01 - height02,
                formula01 <= height01 + height02 + height03 + height04,
                formula01 - height01 - height02 - height03,
                formula01 <= height01 + height02 + height03 + height04 + height05,
                formula01 - height01 - height02 - height03 - height04,
                formula01 <= height01 + height02 + height03 + height04 + height05 + height06,
                formula01 - height01 - height02 - height03 - height04 - height05,
                formula01 <= height01 + height02 + height03 + height04 + height05 + height06 + height07,
                formula01 - height01 - height02 - height03 - height04 - height05 - height06,
                formula01 <=
                    height01 + height02 + height03 + height04 + height05 + height06 + height07 +
                        height08,
                formula01 - height01 - height02 - height03 - height04 - height05 - height06 - height07,
                formula01 <=
                    height01 + height02 + height03 + height04 + height05 + height06 + height07 +
                        height08 + height09,
                formula01 - height01 - height02 - height03 - height04 - height05 - height06 - height07 -
                    height08,
                formula01 <=
                    height03 + height02 + height03 + height04 + height05 + height06 + height07 +
                        height08 + height09 + height10,
                formula01 - height01 - height02 - height03 - height04 - height05 - height06 - height07 -
                    height08 - height09
            ),
            formula04, IFS(
                formula02 <= (height01) * numcols,
                1,
                formula02 <= (height01 + height02) * numcols,
                2,
                formula02 <= (height01 + height02 + height03) * numcols,
                3,
                formula02 <= (height01 + height02 + height03 + height04) * numcols,
                4,
                formula02 <= (height01 + height02 + height03 + height04 + height05) * numcols,
                5,
                formula02 <= (height01 + height02 + height03 + height04 + height05 + height06) * numcols,
                6,
                formula02 <=
                    (height01 + height02 + height03 + height04 + height05 + height06 + height07) *
                        numcols,
                7,
                formula02 <=
                    (height01 + height02 + height03 + height04 + height05 + height06 + height07 +
                        height08) * numcols,
                8,
                formula02 <=
                    (height01 + height02 + height03 + height04 + height05 + height06 + height07 +
                        height08 + height09) * numcols,
                9,
                formula02 <=
                    (height01 + height02 + height03 + height04 + height05 + height06 + height07 +
                        height08 + height09 + height10) * numcols,
                10
            ),
            indx, INDEX((ranges), formula03, SEQUENCE(1,numcols), formula04),
            stacker, IF(blanks = TRUE, FILTER(indx, indx <> ""), indx),
            IF(AND(blanks=TRUE,numcols>1),"#Can not filter empty cells when range has more than one column!",IFS(unique = TRUE, UNIQUE(stacker), unique = FALSE, stacker))
        )
    )

Merge / stack excel columns range definition without VBA

2 Answers