I am making a spreadsheet to compare various lists in various different ways. One of those ways is to take a bunch of separate lists (each in different columns) and combine them into one large column (without losing anything). This allows me to keep things separate while still having the option to VLOOKUP and FILTER and stuff. Maybe this isn't the way to go about it, but it's the way I've gone with so far.

The solution I came up with for now is to QUERY it all as an array (I think that's the right term) and then just select everything that isn't "".

It looks like this though:

=QUERY({Decklists!B8:B;Decklists!C8:C;Decklists!D8:D;Decklists!E8:E;Decklists!F8:F;Decklists!G8:G;Decklists!H8:H;Decklists!I8:I;Decklists!J8:J;Decklists!K8:K;Decklists!L8:L;Decklists!M8:M;Decklists!N8:N;Decklists!O8:O;Decklists!P8:P;Decklists!Q8:Q;Decklists!R8:R;Decklists!S8:S;Decklists!T8:T;Decklists!U8:U;Decklists!V8:V;Decklists!W8:W;Decklists!X8:X;Decklists!Y8:Y;Decklists!Z8:Z;Decklists!AA8:AA;Decklists!AB8:AB;Decklists!AC8:AC;Decklists!AD8:AD}, "select Col1 where Col1 <>''", 0)

Which is really ugly. It works and does what I want, but I'd much prefer it to be clean and work if I add more columns in the future, without having to go and add the additional "call".

If you'd like to look at the whole sheet, it's here:

Explanation

1 - FLATTEN the range needed in this case the Decklists range Decklists!B8:AF.

2 - QUERY the resulted column from the FLATTEN function with the query "select Col1 where Col1 <>''", to get only non empty cells