Merge / stack excel columns range definition without VBA-CodePudding

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!

CodePudding user response：

Since short (currently in ms365's BETA-channels), there is the option to

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.