1

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'

2

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.

7
  • 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. – RamKumar Rangasamy May 10 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/… – RamKumar Rangasamy May 10 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. – RamKumar Rangasamy May 11 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. – RamKumar Rangasamy May 11 at 7:25
  • Sure, Thanks a got. Appreciate it. – RamKumar Rangasamy May 11 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.