1

How can I get a unique list of values from data in Excel where the data is distributed over several columns/ranges? To do so, I would like to use the UNIQUE function (dynamic array function).

Dynamic values using the UNIQUE function in Excel

If I use the following formula:

=UNIQUE(A2:B6)

Excel will just return the rows where both the value in column A and B are identical (which is expected). Is there any way to deliver two separate ranges to the UNIQUE function? I think of something like (does not work!):

=UNIQUE((A2:A6;B2:B6))

Otherwise, I assume a function that converts a two-dimensional range into a one-dimensional range would do the trick. Is there a reasonable way to do that?

I know that there are ways to achieve that use "standard" CSE formulas like this one here, but I would prefer a solution using dynamic arrays.

Thank you!

Michael Wycisk
  • 1,590
  • 10
  • 24
  • 2
    note: TEXTJOIN has a limit and as such if one hits that limit, vba will be the only way to do this automatically, or manually copy and pasting the two columns into one then running Unique on that new column. – Scott Craner Jul 14 '20 at 19:31
  • 2
    I have added a version that does not use TEXTJOIN as avoids the character limit. – Scott Craner Jul 14 '20 at 19:52

0 Answers0