Friday, December 18, 2015

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,
--------------------------------------------------------------------