3

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).

Improve this question
2
  • I think I may be on track to a solution, but I can't seem to get it to work completely. In the query, instead of Select * or Select A,B,C,D,E, I used Select 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.
    – kviLL
    Jul 4, 2018 at 15:37
  • Unmerge cells....'a' will just contain a string as a filler column
    – TheMaster
    Jul 5, 2018 at 14:28

1 Answer 1

Reset to default
0

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")

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.