Converting a multi column range into one column

Question

I have an instance where I would love to be able to get a column of all the unique names in a range. The problem I am having is that the range is multicolumn.

Let's assume my data is in A1:B3 This works fine if I do:

=unique({A1:A3;B1:B3;C1:C3})

But if that range is named and I do

=unique(NamedRange) or =unique(A1:C3)

Then it will spill over the column. Worse if I want to filter or sort the results in the same formula and then run into errors because the formulas want single column/row or the rows/columns don't match anymore.

I don't deal with named ranges a lot, but I did just make a formula today that had 10 columns in it that I stuck into a range like that so that I could do a complex (for me) filter that gave me the difference of two different ranges, similar to: =FILTER({B4:B93;C4:C93;D4:D93;E4:E93;F4:F93}, NOT(COUNTIF(H5:H, {B4:B93;C4:C93;D4:D93;E4:E93;F4:F93}))). I would REALLY love if I could clean that up and make that messy set B4:F instead.

Is there any formula level function that could stick these all in one column? Usually I am looking to do other things with it like sort and filter and the multiple columns get even messier.

Thanks for your time. I DID try searching for this, but I could not seem to find the answer.

Answer 1

Use FLATTEN:

=UNIQUE(FLATTEN(A1:C3))

or

=UNIQUE(FLATTEN(NamedRange))