Tuesday, August 27, 2013

yogi_Query With Totals Max And Latest Transaction For Each Unique Credit Card

                                          Google Spreadsheet   Post  #1346
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Aug 27, 2013
user AlexSFr (http://productforums.google.com/forum/?zx=qk7tzpbpz0qo#!category-topic/docs/spreadsheets/fN4r-xZ1Omc)
Query with totals for each unique entry
So, I have a spreadsheet that has a list of credit card numbers along with dates of transactions made, that looks similar to this:
Card No           Purchase By                Amount                  Date
321352             George                        $35                        02/13/13
321352             George                        $70                        05/13/13
485352             Bill                               $76                        07/13/13
485352             Bill                               $50                        08/13/13
321352             George                        $70                        08/23/13
I would like a query that shows the total transactions made on each card, and what and when the last transaction was made. Example:
Card No              Total Transactions            Last Transaction                   Date of Last Transaction
321352                $175                                 $70                                        08/23/13
485352                $126                                 $50                                        08/13/13
Using query with sum and max I can get the values for Total Transactions and Date of Last Transaction, but I can't get the Last Transaction value to show up.
Any help would be appreciated.