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.


enter image description here

want to list like this:

want to list like this


1 Answer 1


Quick and dirty way :


(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.


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

Your Answer

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

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