Googlesheets Query to substract Sheets

Question

Example - https://docs.google.com/spreadsheets/d/1dNV8BP6AJfc8-u-8BFkkf1i3_QBLe9U1-YfVfUvRdok/edit#gid=1046241405

Sheet - 1 - data is updated daily
Sheet - 2 - data is backed up from 1
Sheet - Diff - is how im getting the difference atm
Sheet - WIP - is what im trying to do

currently im trying to substract values sheet 1 from 2 with

=query('1'!A:N; "SELECT C WHERE (A CONTAINS '"&A2&"') ";0)-query('2'!A:N; "SELECT C WHERE (A CONTAINS '"&A2&"') ";0)

on each cell, it works but its not efficient

on WIP A1, im trying to do it with 1 query

=query({'1'!A:N;'2'!A:N}; "SELECT Col1, sum(Col3) WHERE Col1 is not null GROUP BY Col1";1)

obviously its not sum, but i set as exemple cos i want the oposite, there is no agregator for substraction, so is there a way to call in this example Col3 from each Sheet and make like '1'Col3-'2'Col3 ? or any other way to acomplish it?

user_5632629 · Answer

in general:

3 + 2 = 5

3 + (2 * (-1)) = 1

delete everything in range B2:G and use this in B2:

=INDEX(IFNA(VLOOKUP(A2:A; QUERY({'1'!A:H; '2'!A:B\ '2'!C:H*-1}; 
 "select Col1,sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8) 
  where Col1 is not null 
  group by Col1"); {2\3\4\5\6\7}; )))

to remove zeros you can use:

=INDEX(IFERROR(1/(1/VLOOKUP(A2:A; QUERY({'1'!A:H; '2'!A:B\ '2'!C:H*-1}; 
 "select Col1,sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8) 
  where Col1 is not null 
  group by Col1"); {2\3\4\5\6\7}; ))))

Googlesheets Query to substract Sheets

1 Answers