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).
Select *
orSelect A,B,C,D,E
, I usedSelect A,'a',B,'b',C,'c',D,'d',E
which had the effect of inserting spacer columns containing meaningless information. It would be appreciated if someone could tell me what that was doing (I don't understand why it returned what it did), and why it only ended up returning the first 3 columns, instead of everything A through E.a
string as a filler column