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

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'

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.

• Thanks you so much. It helps a lot. Works perfectly. But when I tried to display Col2 data which is Debit, and also additional fields like Category, I am getting blank data as result. Please help. May 10, 2021 at 14:49
• Thanks a lot for responding. PFB the sheet. I need to list 2% Debit, 1% Debit, 5% Debit separately. I will be making an entry after 2% or 1% credit if amount tallies, rows should be excluded. docs.google.com/spreadsheets/d/… May 10, 2021 at 16:23
• I will be making an entry as 2% or 1% credit after the amount is credited. After 2% or 1% or 5% credit if amount tallies, rows should be excluded. I need only outstanding 2% or 1% or 5% debit rows which needs to be followed up for credit i.e. after debit, I will be looking a credit when my work is done. I will make entry after credit so that debit row automatically removed from overview. May 11, 2021 at 6:00
• Please refer "Copy of Transactions" tab, I31 row table for "2% Debit Status". "Outstanding 2% Debit" should present only when there is value else not row needed. Zeros from M32 to M52 is not needed as we have received the amount. docs.google.com/spreadsheets/d/… Also Flag order needs to be T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11 instead of T1, T10,T11,T2. May 11, 2021 at 7:25
• Sure, Thanks a got. Appreciate it. May 11, 2021 at 10:45