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?– Rubén - Volunteer Moderator - ♦Commented Sep 16, 2019 at 17:02
-
Possible duplicate: Unique Values From a Matrix– Rubén - Volunteer Moderator - ♦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.– marikamitsosCommented Dec 19, 2019 at 16:00
Add a comment
|
1 Answer
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 :)– pjmgCommented Sep 16, 2019 at 23:16
-
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.– pjmgCommented Nov 25, 2020 at 23:18