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

2

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})
  • The quick and dirty way looks slower (more characters to type) than the other way :D – Rubén Sep 16 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 at 23:16

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.