2

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:

enter image description here

want to list like this:

want to list like this

3
4

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

4
  • The quick and dirty way looks slower (more characters to type) than the other way :D
    – Rubén
    Sep 16 '19 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
    Sep 16 '19 at 23:16
  • Love the simplicity of your EDIT here. Thanks
    – Mr_P
    Nov 24 '20 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
    Nov 25 '20 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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