Skip to main content
add a new way to solve it
Source Link
pjmg
  • 2k
  • 4
  • 11
  • 19

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

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

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

Source Link
pjmg
  • 2k
  • 4
  • 11
  • 19

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