Google-sheets – Query returning results into merged cells in Google Sheets

google sheetsgoogle-sheets-query

Short Question:

I have a query function returning an array over merged cells. Instead of displaying all of the values in the array, some of the data is hidden in merged cells where only the first item is shown. I want to expand this array to cover more columns, so that all of the data is visible.

Long Explanation:

I have some data in columns A through E in a sheet labeled "Data". In another sheet, "Query", I'm trying to return that same data with a query, and condensed. I've put the formula =ARRAY_CONSTRAIN(QUERY({Data!A1:E},"Select *"),100,5) in Query!A1. (I know the query isn't actually doing anything here; I'm trying to get this working before I add the 'Where' part)

This formula does not return the data, as it should, from the original sheet. Instead, I only see the first, third, and fifth columns. This is because in the sheet 'Query', columns A/B are merged horizontally, along with C/D and E/F. This causes the data which would have appeared in column B to be hidden. Basically, I need to force the array to be expanded into every other, non-merged, column; instead of returning data in A,B,C,D,E (of which I only see A,C,E), it needs to return data in A,C,E,G,I (so I see all of it).

Best Answer

It's difficult to envision why you would merge entire columns A:B, since that would essentially just create one column; so I'm guessing that only the header columns are merged. If that is the case, there is a simple solution: include at least five blank columns from Data! in your QUERY.

For instance, if you only had data in Data!A:E (i.e., F and following are blank), you could structure your QUERY in Query!A1 like this:

=QUERY(Data!A:J,"Select A, F, B, G, C, H, D, I, E, J Limit 100")

Notice that the Select calls filled column, empty column, filled column, empty column, etc.

However... if you were merging header columns in Query! and trying to fill the left-hand column under each merged header while leaving the right-hand column under each header free to add new data, this approach will not work, since you can't add manual data anywhere inside the scope of a QUERY-filled range.

In that case, the simple solution is simply to place five separate QUERY calls:

In A1: =QUERY(Data!A:A,"Select * Limit 100")

In C1: =QUERY(Data!B:B,"Select * Limit 100")

In E1: =QUERY(Data!C:C,"Select * Limit 100")

In G1: =QUERY(Data!D:D,"Select * Limit 100")

In I1: =QUERY(Data!E:E,"Select * Limit 100")

Related Topic