13

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.

1
  • you can stack columns in array
    – user0
    Jan 14, 2019 at 19:11

3 Answers 3

18

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

1


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

3

7
  • This solution does not work for me. I only get the unique values from column A.
    – HaPsantran
    May 6, 2020 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, 2020 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, 2020 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, 2020 at 12:11
  • @Tobi can you share a copy of your sheet?
    – user0
    Sep 16, 2020 at 20:49
13
=unique(flatten(A1:Z))

I think this will work and it worked for me.

1
  • 1
    That's such a better way to do it! this should be marked as the answer Jan 31, 2022 at 14:46
0

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")))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.