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?

Answer 1

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

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