yogi_From A Table Listing Credit And Debit Transactions In A Single Column rearrange Into Separate Credit And Debit Transactions

Google Spreadsheet   Post  #1993

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-18-2015

post by  Tippy P:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/vI_592Pf3SM;context-place=forum/docs

Conditional Column Value in Query Language for Google Sheets

 

I need a RAW dataset converted to the REPORT dataset.  I had to sanitized the datasets, so some minor errors might exist.  Using '|' pipe character as column delimiter.

RAW dataset:

Date|Description|Amount|Transaction Type|Labels

11/30/2015|Target|9.93|debit|cvs

11/30/2015|Bar-B-Que|8.77|debit|cvs

11/20/2015|PayCheck|1,000.00|credit|cvs

...

REPORT dataset:

Date|Description|Debit|Credit

11/30/2015|Target||(9.93)

11/30/2015|Bar-B-Que||(8.77)

11/20/2015|PayCheck|1,000.00|

...

ACTUAL dataset results:

Date|Description|Debit|Credit

11/30/2015|Target|9.93|(9.93)

11/30/2015|Bar-B-Que|8.77|(8.77)

11/20/2015|PayCheck|1,000.00|(1,000.00)

...

ACTUAL Query:

=Query(M_2015_12!A:I,"select A, B, C*1, C*-1, E where E contains 'cvs' order by A label C*1 'Debit', C*-1 'Credit'", -1)

Is there a way to put conditions in the columns? Like:

=Query(M_2015_12!A:I,"select A, B, IF(debit,D*1,0), If(credit,0,D*-1), H where H contains 'cvs' order by A label D*1 'Debit', D*-1 'Credit'", -1)

Thanks,

--------------------------------------------------------------------