# How can I create a unique list of data across multiple rows AND columns?

Google Sheets has a UNIQUE() function which when given a column of values, will output a column of all unique values.

This works well for column-specific data, however, you have multiple columns and rows of data that needs to be summarized, this won't work. This is because if you include a 2-dimensional range into UNIQUE() the end results are two dimensional, with only each column being summarized.

Is there a function or way to summarize all values across an entire 2-dimensional range?

REASONING:

I have a large list of businesses. Each column is a type of business, and each row contains values of business names. I need to create an end-list of all businesses that are provided throughout the entire source sheet. I don't need unique values per column. I need unique values across the entire document.

• you can stack columns in array – user0 Jan 14 '19 at 19:11

=UNIQUE({A:A;B:B;C:C})

=ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;B:B;C:C}),
"select Col1 where Col1 is not null")))

• This solution does not work for me. I only get the unique values from column A. – HaPsantran May 6 '20 at 15:36
• is there anything if I have 100 columns and don't want to add them all manually? A:A;B:B – Tobi Sep 16 '20 at 10:21
• @Tobi you may try =ARRAYFORMULA(FLATTEN(A:Z)) or if row order matters: =ARRAYFORMULA(TRANSPOSE(QUERY(TRANSPOSE(A:Z),,9^9))) – user0 Sep 16 '20 at 10:57
• @user0 thanks =unique(ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(Main!C:Z),,9^9))))) works somehow... but its not splitting the columns/cells per row. – Tobi Sep 16 '20 at 12:11
• @Tobi can you share a copy of your sheet? – user0 Sep 16 '20 at 20:49
=unique(flatten(A1:Z))


I think this will work and it worked for me.

I think in your case, you may want to stack the columns vertically. You can do that by adding a semi-colon between them, like this:

=ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({C:C;D:D;E:E;F:F}),
"select Col1 where Col1 is not null")))