1

I have two Goole Sheet queries which both work as required my remaining challenge is to combine the results of both and order by date. my two queries are:

=UNIQUE(FILTER({'My Data'!B13:B, 'My Data'!F13:F, 'My Data'!N13:N, 'My Data'!L13:L, 'My Data'!L13:L * 24 * 'My Data A'!K2 }, 
'My Data'!O13:O = "Y", 
'My Data'!Q13:Q =G2,
MONTH('My Data'!B13:B) =E2, 
YEAR('My Data'!B13:B) =C2
))

and

=UNIQUE(FILTER({'My Data 2'!A4:A, 'My Data 2'!E4:E, 'My Data 2'!D4:D, 'My Data 2'!F4:F,'My Data 2'!F4:F * 24 * 'My Data 2'!G4:G}, 
'My Data 2'!C4:C =G2, 
MONTH('My Data 2'!A4:A) =E2, 
YEAR('My Data 2'!A4:A) =C2
))

I can not see a way of doing what I require other than creating an extra sheet with both queries on and then querying that sheet but these still doesn't give me what I need!

Is there a way or an approach to do this in sheets?

player0
  • 124,011
  • 12
  • 67
  • 124
Adrian
  • 1,089
  • 24
  • 51
  • Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables makes **it is easier to copy/paste**. If you share spreadsheets/images as the only source of data, your question may be closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 28 '22 at 22:56

1 Answers1

2

try:

=SORT({UNIQUE(FILTER({'My Data'!B13:B, 'My Data'!F13:F, 'My Data'!N13:N, 'My Data'!L13:L, 'My Data'!L13:L * 24 * 'My Data 2'!K2 }, 
'My Data'!O13:O = "Y", 
'My Data'!Q13:Q =G2,
MONTH('My Data'!B13:B) =E2, 
YEAR('My Data'!B13:B) =C2
));
UNIQUE(FILTER({'My Data 2'!A4:A, 'My Data 2'!E4:E, 'My Data 2'!D4:D, 'My Data 2'!F4:F,'My Data 2'!F4:F * 24 * 'My Data 2'!G4:G}, 
'My Data 2'!C4:C =G2, 
MONTH('My Data 2'!A4:A) =E2, 
YEAR('My Data 2'!A4:A) =C2
))})

update:

=SORT({UNIQUE(FILTER({'My Data'!B13:B, 'My Data'!F13:F, 'My Data'!N13:N, 
 'My Data'!L13:L, 'My Data'!L13:L * 24 * Totals!K2 }, 'My Data'!O13:O = "Y", 
'My Data'!Q13:Q =G3, MONTH('My Data'!B13:B) =E3, YEAR('My Data'!B13:B) =C3));
 UNIQUE(FILTER({'Non Log'!A4:A, 'Non Log'!E4:E, 'Non Log'!D4:D, 'Non Log'!F4:F,
 'Non Log'!F4:F * 24 * 'Non Log'!G4:G}, 'Non Log'!C4:C =G3, 
 MONTH('Non Log'!A4:A) =E3, YEAR('Non Log'!A4:A) =C3))}, 1, 0)

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Hi Thank you for your help, unfortunately, that doesn't seem to work there seemed to be an extra } which I removed at the end, but that changed from a parsing error to 'SORT expects all arguments after position 1 to be in pairs.' – Adrian Oct 28 '22 at 20:37
  • @Adrian fixed, try now – player0 Oct 28 '22 at 20:38
  • If I add a { after the opening ( i now get: 'Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 3. Actual: 1.' which is correct in a way, in that the first part returns 3 rows whereas the second returns 1, these amount returned by each will very. – Adrian Oct 28 '22 at 20:44
  • @Adrian can you share a copy / sample of your sheet with an example of the desired output? – player0 Oct 28 '22 at 20:45
  • Sorry took a while to get a stripped version i could share https://docs.google.com/spreadsheets/d/1HFAtIwQmbNYoCSWgeKmHSNBvmDsJIXtDWhQhe1GXd-o/edit?usp=sharing the invoicing tab G7 - K7 i would like to be contained with in the data B7 - F7 – Adrian Oct 28 '22 at 21:19
  • example of the results i would like are in 'results wanted' tab – Adrian Oct 28 '22 at 21:25