Google Sheets – Combine Text from Rows by Specified Date

google sheets

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

Best Answer

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