2

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!

Chris
  • 83
  • 5

1 Answers1

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:

enter image description here

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.

JvdV
  • 53,146
  • 6
  • 36
  • 60
  • 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