# Need unique values of a range in google sheets

I need to list unique values of 12 columns in a different sheet columns.

Like sheet 1 has A column.

I need to list the unique values of all 12 columns in Sheet 2 to Sheet1 A column.

Like:

want to list like this:

• Will always the values be single letter / word? Commented Sep 16, 2019 at 17:02
• Possible duplicate: Unique Values From a Matrix Commented Sep 16, 2019 at 17:06
• Please remember that as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. Commented Dec 19, 2019 at 16:00

Quick and dirty way :

``````=UNIQUE(ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(A1:F12&"🐥"),"🐥"))))
``````

(Source : organizing google-sheets data range into single column)
However, care must be taken not to exceed 50k characters when using `concatenate`. If the case arises, simply break the formula into several pieces or use arrays.

``````=unique({A1:A10;B1:B10;C1:C10})
``````

Edit : I have just learned that this problem can now be solved in an even simpler way. `=unique(flatten(A1:F12))`

• The quick and dirty way looks slower (more characters to type) than the other way :D Commented Sep 16, 2019 at 17:03
• Yeah, I didn't give it a second thought, that was pretty dumb :o. But I still think solution 1 is easier because there's only an unique range to type. I'm confused and lost :)
– pjmg
Commented Sep 16, 2019 at 23:16
• Love the simplicity of your EDIT here. Thanks
– Mr_P
Commented Nov 24, 2020 at 9:20
• Do understand that FLATTEN is a not-yet-official Google Sheets function. As such, using it comes with the risk that Google may — or may not — choose to add it to the official functionality.
– pjmg
Commented Nov 25, 2020 at 23:18