What's the formula to select non matching rows by comparing rows in Google Sheets query?

Question

In Google Sheets query, I need to get only non matching debit rows by comparing rows say if amount debited (S1 flag - row 1) matches with amount credited (S1 flag - row 3), it needs to be excluded in the result so I only get know the outstanding debit amount which needs to be credited.

Flag	Transaction	Amount
S1	Debit	10
S1	Debit	10
S1	Credit	10
S2	Debit	20
S3	Debit	50
S3	Credit	50

Result needs to be look like below:

Flag	Transaction	Amount
S1	Debit	10
S2	Debit	20

My Basic query:

=QUERY(K2:M9,"select K, L, M")

where K is not null and L = 'Debit'

Answer 1

To answer your question, you can turn the source data table above to the result table above with this formula:

=arrayformula( 
  query( 
    query( 
      { A1:A, if(regexmatch(B1:B, "(?i)Credit"), -C1:C, C1:C), B1:B }, 
      "select Col1, sum(Col2) 
       where Col1 is not null 
       group by Col1", 
      1 
    ), 
    "select Col1, 'Debit', Col2 
     where Col2 <> 0 
     label 'Debit' 'Transaction' ", 
    1 
  ) 
)

But your sample spreadsheet tells that you ultimately need to consider labels 1% Debit, 2% Debit and 5% Debit each separately. You can do that with an array formula that massages the data based on whether a row is Debit or Credit, and aggregate the massaged data with a query() that uses a group by clause. To weed out rows with a zero result, and create columns for each of 1% Debit, 2% Debit and 5% Debit separately, wrap the query() in another query() that uses a pivot clause, like this:

=arrayformula( 
  query( 
    query( 
      { A1:A, if(regexmatch(E1:E, "(?i)Credit"), -G1:G, G1:G), E1:E }, 
      "select Col1, sum(Col2), Col3
       where Col1 is not null 
       group by Col1, Col3", 
      1 
    ), 
    "select Col1, sum(Col2) 
     where Col2 <> 0 
     and lower(Col3) contains 'debit' 
     group by Col1 
     pivot Col3", 
    1 
  ) 
)

To calculate per-flag totals, use this:

=arrayformula( iferror( 1 / ( 1 / sumif( if(column(K7:N29), row(K7:N29)), row(K7:N29), K7:N29 ) ) ) )

See cells K6 and J7 in your sample spreadsheet.

Note that you can use open-ended references like A1:A that will evaluate rows all the way to the bottom of the sheet only when there are no more results or data below the result table. To limit it to some fixed number of rows, use A1:A500 and so on.