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("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE(SRC!$A$1:$C$22)),",","</b><b>")&"</b></a>","//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!

JvdV · Accepted Answer · 2022-03-24T12:12:48.113

2

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("<t><s>"&TEXTJOIN("</s><s>",,A2:A5,C3:C7)&"</s></t>","//s"))

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

edited Mar 24 '22 at 12:12

answered Mar 24 '22 at 12:00

JvdV

70,606
8
39
70

1

Sir, just applied these two as well, worked wonderful, out of my curiosity I tried this two as well, `=UNIQUE(TOCOL($A$2:$C$7,3,1))` & `=UNIQUE(TOCOL(HSTACK(A2:A5,C3:C7),3,1))` – Mayukh Bhattacharya Mar 24 '22 at 12:54
1

@MayukhBhattacharya, `TOCOL()` is not that handy in this case as I assume OP has data in the columns he would rather skip. – JvdV Mar 24 '22 at 12:58
Ah okay, understood sir, yes you are correct. But can't we ignore the same within a `TOCOL()` may be possible – Mayukh Bhattacharya Mar 24 '22 at 13:00
Unfortunately, Vstack is not available on my machine, yet, but the Textjoin works fine with multiple ranges / texts. Thanks! Do you know a method how I can combine n ranges into one (virtual) range that I can use in formulas because there are a lot of formulas that only accept 1 range parameter? – Chris Mar 24 '22 at 14:06
@Chris you are welcome. For the 2nd part of your comment; that is possible with ms365 newest functions as demonstrated. If not yet access to `VSTACK()` you'll end up in a ever increasingly difficult web of functions (also only for ms365). – JvdV Mar 24 '22 at 14:28

Tom Tommarello Jr · Answer 2 · 2022-07-20T06:13:26.773

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 Answers2

Linked

Related