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,

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

yogi_Compute By item In Column B Sum Of Numbers In Column C By Specified Month(s)

Google Spreadsheet   Post  #1992

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

post by  Gilbert sampayan:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/I3kQjO-RYCk;context-place=forum/docs

Filtering data using a formula

is it possible to have a formula to automatically filter my data so it will only return data from a given range?

Example:

I need to get total Count per Type only from 12/01/2015 to 12/31/2015 then from 01/01/2016 to 01/31/2015

https://docs.google.com/spreadsheets/d/1q7plr0lW7Jt0FFLNRADGgGcdb6bbW35ydlI1mJ29wC4/edit?usp=sharing
------------------------------------------------------------------------------------------

yogi_Compute Row By Row Sum Of Entries In Columns B Through F For Condition Spelled Out In Cell G1

Google Spreadsheet   Post  #1991

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

post by  bbiagllla:
https://productforums.google.com/forum/#!topic/docs/-RQ225qEEyg;context-place=mydiscussions
Working with If(and(or at the same time kind of

Ok i need to add up multiple columns based on criteria. Basically i need to take 5 columns and give and if statement to add a number if 2 of them are over 9 and/or 3 of them are over 9. I will attach a link

Test Sheet

in this sheet i need the following to happen

=B2+(C2*1.5)+(D2*1.25)+(E2*2)+(F2*2)+or(if(or(and(B2,C2),and(B2,D2),and(B2,E2),and(B2,F2),and(C2,D2),and(C2,E2),and(C2,F2),and(D2,E2),and(D2,F2))>9,2,0),if(or(and(B2,C2,D2),and(B2,D2,E2),and(B2,E2,F2),and(B2,C2,E2),and(B2,C2,F2),and(C2,D2,E2),and(C2,E2,F2),and(D2,E2,F2))>9,3,0))

Edited

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

yogi_Pull Names Of Donors That Contributed In Specified Month And Year Of Last Year And Have Not Contributed Since Then

Google Spreadsheet   Post  #1990

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

post by  Steve Thomasson:
https://productforums.google.com/forum/#!topic/docs/XHbkRGp0rKk;context-place=mydiscussions
Help with Query

Hi, I hope someone can help.
I have a Google sheets that has a list of donors and the dates of donations and their contact information.  Some donors donate multiple times per year.  I am trying to use query to generate a list of people who donated in the current month one year ago, but EXCLUDE any names that may have donated more recently.   We are trying to generate a list of people to contact to see it they would be willing to donate again but don't want to bother them if they have already donated more recently than one year.

Any ideas on how to do this would be appreciated.
Steve

This is what I have so far:
=query('Donors & Sponsors'!A4:H, "select A, sum(F) where A <> '' and date '" & text(B1, "yyyy-mm-dd") & "' <= toDate(C) and toDate(C) <= date '" & text(C1, "yyyy-mm-dd") & "' group by A label A 'Name', sum(F) 'Total contribution' ",1)

This gives me a list of donors that gave a year ago but does not exclude any people have donated more recently.  I cannot figure out how to do that.  Someone please help.

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

yogi_Query Data From Number Of Location Sheets For Summing By Specified Attributes And Location

Google Spreadsheet   Post  #1989

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

post by  John Luna:
https://productforums.google.com/forum/#!topic/docs/pzhg8_IwWu0;context-place=mydiscussions
Importing data from Multiple Sheets and creating an report.

Hi, I am helping a non-profit which helps homeless people with get job training. They have a project which lets them earn money working for them while they are working towards their goals. What they need is a workbook with multiple sheets which represent locations. We would like to collect info from each sheet and create a report. I would also like to sort by a Date Range and by location. I am new to Google Sheets and having a problem figuring it out.

We have 8 (locations)Tabs, each have same 3 columns name Date, Cig Butts, and Trash Bags. This workbook will have a year worth of data. I would like a report that can sort by week, month, and a year. Where we can combined the data from each location show each location total.

I thought the location could go down one column and the items label across. 

I figure out how to do it in excel using a Pivot Tables but its not as easy in Google Sheets. I been watching a lot of videos but feel stuck.Thanks for your help. 

Sunnyvale Environmental Metrics V4

___________________________________________________________________________________________________

yogi_From raw_responses sheet Pull Latest N Records For Each Unique Name

yogi_Create Filter View With Dynamic Updating Of Computed Stats Such As Average Age Of Select Gender(s) Living In Specified Town(s)

Google Spreadsheet   Post  #1987

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

post by  Mark Erhard:
https://productforums.google.com/forum/#!topic/docs/jxn4nk77Acs;context-place=forum/docs
Calculation depending on Filters in Sheets
Hi, i have a List of People with a few columns, like Age, Gender, Town and other factors.

Now i have a filter view and want to be flexible and sometimes filter for all females, from town A.

I have a row below that list, that shows me for example the average Age of all in the List.

Now if i use the filter, the results of the Average Age doesnt change.
It still shows the average age of the whole group.

Is there a way to change that and to calculate the result depending on the filter???

Would be a great help!!!!

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