Search code examples
google-sheetsmergegoogle-sheets-formulagoogle-sheets-query

Building a statement

SOLVED!!!!!!!

I have the following sheet that I'm working with:

Data Sample

I'm looking to build a transaction details statement like this one:

Output Required

Now comes the fun part - The Query

I have tried to merge two Google queries, but I am having a problem with the headers that come up because of the empty column that I need to have on each query.

This is what I have so far:

={query({Sheet3$A$1:$F},"Select Col1, Col3,' ', Col6 where Col2= '"&B1&"'",1);query({Sheet3!$A$2:$F},"Select Col1, ' ', Col5, Col6 where Col4= '"&B1&"'",0)}

Problems:

  1. I can tweak it to get the headers I want in query 1, but query 2 gets a weird header (" "() ) Like this one - because of the empty column.
  2. The two queries appear together, but I cannot merge them successfully i.e. in order of time logs.

The solution was the following query:

=sort({query({Sheet3!$A$1:$F},"Select Col1, Col3,' ', Col6 where Col2= '"&B1&"' label ' ' '' ",0);query({Sheet3!$A$2:$F},"Select Col1, ' ', Col5, Col6 where Col4= '"&B1&"' label ' ' ''",0)},1,TRUE)

This removed all headers and also put the data together without the annoying empty row in the middle. ALSO.. adding sort(full query,1,TRUE) allowed me to sort the records by the 1st column which achieved the desired result.

Lastly - i added the headers manually which was absolutely fine as these will never change or require any updates.

THANK YOU ALL!!!!

Solution

  • By default QUERY includes headers. In order to prevent that QUERY add headers for your second QUERY you could use the label clause to replace the default label for the ' ' column by and empty string label ' ' ''

    The resulting second QUERY looks like this:

    query({Sheet3!$A$2:$F},"Select Col1, ' ', Col5, Col6 where Col4= '"&B1&"' label ' ' ''",0)