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
.
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
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 theARRAYFORMULA
. Each row is then joined, via theJOIN
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