Google Sheets - Combine multiple columns into one with unique values

Question

I currently have 6 columns that are populating from another sheet with the query formula, based on column name. I would like to combine these 6 columns into one column, eliminating duplicates, and listing them all in one-singular column. I've tried the following without success:

FILTER(UNIQUE(A2:A, B2:B, C2:C, D2:D, etc.),ISBLANK({same list})=FALSE))
FLATTEN(TRANSPOSE(A2:A, B2:B, C2:C, etc.))
FILTER(UNIQUE({A2:A,B2:B;C2:C; etc.}), LEN(UNIQUE({A2:A,B2:B;C2:C; etc.})))

The catch seems to be that sometimes the columns will be blank. I think google sheets is either reading them as blank and giving me an error, or sees there a query'd set of data and it's unable to retrieve the list.

Any recommendations?

Erik Tyler · Accepted Answer · 2022-02-28 20:04:53Z

3

This should work, if I'm understanding you correctly:

=QUERY(FLATTEN(A2:F),"WHERE Col1 Is Not Null")

answered Feb 28, 2022 at 20:04

Erik Tyler

9,3952 gold badges9 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brian Over a year ago

This worked! Originally did not because I tried to use all caps for "Col1". Thanks!

Erik Tyler Over a year ago

Glad you got it working.

Erik Tyler Over a year ago

Using newer functionality made available since the original post, this would be more succinct: =TOCOL(A2:F, 3)

Collectives™ on Stack Overflow

Google Sheets - Combine multiple columns into one with unique values

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related