Combine text from rows got by specified date in a Google Spreadsheet

Question

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;
}

Answer 1

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

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

Answer 2

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 ;) )