Google Sheets – Subtract Range of Values by Matching Column Entries

formulasgoogle sheets

I made two queries(using =QUERY) to a range of data in google sheets with the results as per the image below

I would like to:

subtract the values of the right/smaller table from the left/bigger table according to the
matching column entries (B,GBP)
add the column entry(ies) of the right/smaller table that are missing from the left/bigger table

with the desired result looking like :

Best Answer

Try this:

=query({query(A2:B7);query({D2:E5},"select Col1, -1*Col2 label -1*Col2 ''")}, "select Col1,Sum(Col2) group by Col1 Label Sum(Col2) 'Sum'")

Related Solutions

Google Sheets – How to Paste Cell Range Automatically as Values Only

You could use an onEdit trigger for the script. That will update whenever changes are made. For example:

/**
 * A function named onEdit will be called whenever
 * a change is made to the spreadsheet.
 * 
 * @param  {object} e The edit event (not used in this case)
 */
function onEdit(e){
  var copyFromRange = 'Sheet1!A13:A'; // no row for second cell reference
  var copyToRangeStart = 'Sheet2!A3';
  copyValuesOnly(copyFromRange, copyToRangeStart);
}

/**
 * This function will copy the values from a given range to
 * a second range, which starts from the given cell reference
 * 
 * @param  {string} copyFromRange    Range reference eg: 
 * @param  {string} copyToRangeStart Cell reference eg:
 */
function copyValuesOnly(copyFromRange, copyToRangeStart) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getRange(copyFromRange);
  source.copyTo(ss.getRange(copyToRangeStart), {contentsOnly: true});
}

See https://developers.google.com/apps-script/understanding_triggers for more details on triggering scripts automatically.

I can't comment yet, so will ask here: Do you need to copy to a second spreadsheet or just a second sheet within the same spreadsheet? The second case is answered above, but I can adjust if you need to copy to a separate spreadsheet?

edit: added code to fully answer the question. Thanks for the guidance Jacob.

Google-sheets – “Split” delimited string, preserving blank entries

Try the following solution.

Formula

=ARRAYFORMULA(TRIM(SPLIT(JOIN(" ,", A1:A4,C1:C4), ",")))

Explained

You already showed us that the SPLIT function ignores empty cells, therefore I choose this string as delimiter: " ,". The intermediate result of the JOIN function, looks like this:
enter image description here

I choose the delimiter for the SPLIT function to be this: ",". The intermediate result of the SPLIT function, looks like this:
enter image description here

Now, however, we have to deal with the extra added white spaces. Therefore I used the TRIM function, to remove all trailing or leading white spaces (in combination with the ARRAYFORMULA). Compare the LEN values of each intermediate result.

Result

enter image description here

Example

I've created an example file for you: SPLIT to preserve blank cells