Get Unique Range from Multiple Dynamic Named Ranges

Question

I have two named ranges that are dynamic in length that I would like to combine into one range with just the unique values from the two. In other words, I essentially am hoping to get a union of the two ranges and then get the unique values. Below is a small toy example to demonstrate. I'm hoping to find a solution without using VBA, etc.

Two dynamic named ranges:

And the desired result would be:

You need `VSTACK()` function if your version of excel support it. — Harun24hr, May 19 '22 at 04:17
I don't think I have access to `VSTACK()` with Microsoft 365? — ErrorJordan, May 19 '22 at 04:29
Duplicate: https://stackoverflow.com/q/69837142/16578424 - there are answers w/o FILTERXML — Ike, May 19 '22 at 05:49
Your title says *multiple* Named Ranges but then you appear to require only two. Can you clarify? A tailored solution for just two may be more feasible than a generic solution for any number. — Jos Woolley, May 19 '22 at 05:58

score 3 · Answer 1 · answered May 19 '22 at 04:21

3

With Microsoft 365 you can try-

=UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,name1,name2)&"</s></t>","//s"))

answered May 19 '22 at 04:21

Harun24hr

30,391
4
21
36

Appreciate the quick response. Unfortunately this isn't working for me. Possibly because my named ranges are dynamic? Not sure...but I'm just getting an error when I replace `name1` and `name2` with my named ranges. – ErrorJordan May 19 '22 at 04:32
Dynamic shouldn't be problem. Can you try direct range instead of named range for troubleshooting purpose? – Harun24hr May 19 '22 at 04:36
1

Another thing, if you are on Macbook then excel wouldn't support `FILTERXML()` function. – Harun24hr May 19 '22 at 04:37
Ah, yes, I am on a Macbook. – ErrorJordan May 19 '22 at 04:42
1

Very clever to TEXTJOIN with a delimiter of `"~~"`! Well done,~~ – mark fitzpatrick May 19 '22 at 06:24

Get Unique Range from Multiple Dynamic Named Ranges

1 Answers1