3

I am trying to create a summary sheet that combines the values and descriptions from my bank transaction into individual dates

I want to paste the transaction csv into google spreadsheets. The columns are Date, Description, CheckNumber and Amount.

Here is my example sheet

What I want would be for Summary!D2 to combine the description of all rows that have the date 2/1/2014 (shown in Summary!A2) like this (including line feeds).

EDIT - Switched to Jacobs Method

Final code (Removes N/A error when nothing found in query):

=JOIN(CHAR(10),ARRAYFORMULA(IFERROR(QUERY(Transactions!A2:D, "SELECT B WHERE B IS NOT NULL AND A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'") & ": ") & IFERROR(QUERY(Transactions!A2:D, "SELECT D WHERE D IS NOT NULL AND A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"))))

EDIT - Added amounts to lines of note

PartTime Payroll: 182.7 
Academy Payroll: 81.13
Recurring purchase at netflix.com: -7.99
ONLINE TRANSFER TO CC: XXXXXXXXXX: -49

The values in Summary are already showing what I want, which in this case is $206.84 which is the result of the Amounts with the date 2/1/2014 from Summary!A2, (182.7, 81.13, -7.99, -49).

EDIT - I figured it out


function updateNote(cellDate){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sumSheet = ss.getActiveSheet();
  var transSheet = ss.getSheetByName('Transactions');
  var transSheetLastRow = transSheet.getLastRow();

  cellDate = cellDate.valueOf();
  var currDate;
  var note;
  var text;
  var amount;
  var space = ": ";

  for (var i = 2; i < transSheetLastRow; i++){
    var currDate = transSheet.getRange(i, 1).getValue().valueOf();

    if(cellDate == currDate){
      text = transSheet.getRange(i, 2).getValue();
      amount = transSheet.getRange(i, 4).getValue();

      if (!note){
        note = text + space + amount;
      }else{
        note = note + '\n' + text + space + amount;
      }
    }
  }
  return note;
}
  • I updated my answer. Note the added equal sign in the for loop. for (var i = 2; i <= transSheetLastRow; i++){ It needs to be <=, previously it was only <. – Punchlinern Mar 27 '14 at 23:44
  • Updated again, the notes weren't updating properly. – Punchlinern Mar 28 '14 at 14:27
3

I would use a formula to accomplish your goal.

Formula

=JOIN(
   CHAR(10),
   ARRAYFORMULA(
     QUERY(
       Transactions!A2:D, 
       "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     ) 
     & ": " &   <=== concatenate
     QUERY(
       Transactions!A2:D, 
       "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     )
   )
 )

 copy/paste         
 =JOIN(CHAR(10),ARRAYFORMULA(QUERY(Transactions!A2:D, "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'") & ": " & QUERY(Transactions!A2:D, "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'")))

Explained

The two QUERY functions, both deliver each part of the data you want to present: the description and the value. These two are then "glued" together, via the ampersands and per row, via the ARRAYFORMULA. Each row is then joined, via the JOIN function, using a line feed (CHAR(10)) as delimiter.

Screenshot

enter image description here

Note

The huge benefit over a script is the immediate re-calculation of the data, when changes occur. Just copy the formula downwards to get all the results. You can add your own logic to remove empty fields.

Example

I've prepare an example file for you: Notes

3
+50

You can accomplish this with a script:

var transSheetName = 'Transactions';
var sumSheetName = 'Summary';

function updateNote(cellDate){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sumSheet = ss.getSheetByName(sumSheetName);
  var transSheet = ss.getSheetByName(transSheetName);
  var transSheetLastRow = transSheet.getLastRow();

  cellDate = cellDate.valueOf();
  var currDate;
  var note;
  var amount;
  var space = ': $';

  for (var i = 2; i <= transSheetLastRow; i++){
    var currDate = transSheet.getRange(i, 1).getValue().valueOf();
    if(cellDate == currDate){
      amount = transSheet.getRange(i, 4).getValue();
      if (amount < 0){
        amount = -amount;
        space = ': -$';
      }

      if (!note){
        note = transSheet.getRange(i, 2).getValue() + space + amount;
      }else{
        note = note + '\n' + transSheet.getRange(i, 2).getValue() + space + amount;
      }
    }
  }
  return note;
}




function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();
  var row = range.getRow();
  var column = range.getColumn();

  if (sheet.getName() == transSheetName && row > 1){
    var date = sheet.getRange(row, 1).getValue();
    var note = sheet.getRange(row, 2).getValue();
    var amount = sheet.getRange(row, 4).getValue();

    if (date != '' && note != '' && amount != ''){
      Browser.msgBox('Running...');
      //return false;
      var sumSheet = ss.getSheetByName(sumSheetName);
      var currDate = date.valueOf();
      var cellDate;
      for (var i = 2; i <= 32; i++){
        cellDate = sumSheet.getRange(i, 1).getValue().valueOf();
        if (cellDate == currDate){
          sumSheet.getRange(i, 4).setFormula('=updateNote(A' + i + ', ' + transSheetName + '!B' + row + ')');
        }
      }
    }
  }


  if (sheet.getName() == sumSheetName && row == 1 && column == 1){
    var transSheet = ss.getSheetByName(transSheetName);
    var transSheetLastRow = transSheet.getLastRow();
    var i = 2;
    for (; i <= 32; i++){
      sheet.getRange(i, 4).setFormula('=updateNote(A' + i + ', ' + transSheetName + '!A' + transSheetLastRow + ':D' + transSheetLastRow + ')');
    }
  }
}

The script is called from the cells in the D-column. You call it with the cell containing the date on the same row as parameter. So in D2 you write =updateNote(A2), in D3 you write =updateNote(A3) and so forth.

It's rather annoying having to enter the function in every cell, so I wrote a script for that too. Run it once and never again:

function fill(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  if(sheet.getName() == 'Summary'){
    for (var i = 2; i < 32; i++){
      sheet.getRange(i, 4).setFormula('=updateNote(A' + i + ')');
    }
  }
}

Copy this sheet to try it.


Edit

The fill-function shouldn't be necessary anymore, just switch to another month in the summary-sheet and the onEdit function will fix that.

Also, Jacob's answer is much faster, and after my edit even more simpler. So I'd recommend using that. (I continue editing this partly because I feel I owe you that for giving me the bounty, and partly because I enjoy coding ;) )

  • This looks like it fits the bill. You definitely get the bounty. However now that I am seeing the results I am wondering, is it difficult to append the amount to the note? See modified question. – gooddadmike Mar 27 '14 at 16:09
  • Figured it out! – gooddadmike Mar 27 '14 at 16:36
  • Great, thank you! I updated my code after your edit. It's quite similar to your solution, just that it includes dollar signs in the note. – Punchlinern Mar 27 '14 at 16:43
  • -1; The total amount of notes is 8. Your summary shown only 7. In order to re-calculate, you need to add onEdit() functions as well, throughout the TRANSACTIONS sheet. – Jacob Jan Tuinstra Mar 27 '14 at 21:04
  • Why don't you join me in this chat room: chat.stackexchange.com/rooms/13056/spreadsheet-nerds – Jacob Jan Tuinstra Mar 27 '14 at 21:37

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.