1

I have two ranges B4:B10 and C5:C10 as follows. I'm looking for a formula to calculate the union of the two ranges (so there will be no duplicates in the result).

enter image description here

At the moment, my formula is as follows. It applies UNIQUE to the concatenation of the 2 ranges. But I'm wondering if there is a better (eg, shorter) formula to do it.

= UNIQUE(LET(
           la,
           B5:B10,
           lb,
           C5:C10,
           i,
           SEQUENCE(ROWS(la) + ROWS(lb)),
           r,
           IF(i <= ROWS(la), la, INDEX(lb, i - ROWS(la))),
           r))

Could anyone help?

PS: I would prefer a solution without LAMBDA function and its helper functions.

JvdV
  • 70,606
  • 8
  • 39
  • 70
SoftTimur
  • 5,630
  • 38
  • 140
  • 292
  • I don't think it gets better than this by formula – P.b Jan 07 '22 at 07:49
  • 1
    Seems about right to me without `LAMBDA()`. The only other thing is `FILTERXML()` & `TEXTJOIN()`. See [this](https://stackoverflow.com/a/62205206/9758194). – JvdV Jan 07 '22 at 09:43
  • Nice different approach, but limited to Windows environment. – P.b Jan 07 '22 at 12:09
  • I think this is a duplicate: https://stackoverflow.com/q/69837142/16578424 – Ike Jan 13 '22 at 08:24

1 Answers1

2

The code I use for a 2D combined unique list is the following but it is calculation intensive with large data sets (and it is definitely not shorter), but it does work with any size of 2D data range:

=LET(array, Data,
     list, INDEX(array, ROUNDUP(SEQUENCE(ROWS(array)*COLUMNS(array))/COLUMNS(array),0), MOD(SEQUENCE(ROWS(array)*COLUMNS(array),,0), COLUMNS(array))+1),
     UNIQUE(FILTER(list, LEN(list)>0)))

Where Data is a dynamic named range for me

However: having just researched the question a little; this is achievable via a pivot table; for the data area, you need to include a blank row above and blank column to the left where labels would be in a table

https://www.extendoffice.com/documents/excel/1966-excel-extract-unique-values-from-multiple-columns.html

Tragamor
  • 3,594
  • 3
  • 15
  • 32