Combine transactions from two different sheets into one overview?

asked 2018-10-17 09:23:29 +0200

RagusLive gravatar image

I use LibreOffice Calc to track my spending by importing the raw spreadsheet data downloaded from my banks website.
Checking account transactions come in one file, credit card transactions come in another separate file. I take the data from these 'raw' files and paste it into the "Input" sheets of the Calc file that I built (and have used over the past year).
The Calc file I built has been very useful, but has always been a bit hamstrung by the fact that transactions on the two accounts cannot be calculated in the same 'pile'.

If the two raw sheets read something like:
CHECKING:

CHK 07/03/18    -36.5
CHK 07/03/18    -500
CHK 07/03/18    -10.11
CHK 07/03/18    -4010
CHK 07/05/18    -210.03
CHK 07/05/18    -96.54
CHK 07/05/18    -200
CHK 07/05/18    -5.99
CHK 07/06/18    -80.01
CHK 07/09/18    -200
CHK 07/09/18    -49.27
CHK 07/09/18    -11.9
CHK 07/09/18    -17.06
CHK 07/09/18    -9.5
CHK 07/09/18    -90.59
CHK 07/09/18    -366.63

CREDIT:

CC  07/03/18    -19.2
CC  07/04/18    -20.94
CC  07/04/18    -9
CC  07/04/18    -10.28
CC  07/04/18    -38.36
CC  07/05/18    -15.95
CC  07/05/18    -16.23
CC  07/09/18    366.63
CC  07/09/18    -43.13
CC  07/09/18    -7.35
CC  07/09/18    -54.77

Then I would like the output sheet to read something like:

CC  07/03/18    -$19.20
CHK 07/03/18    -$36.50
CHK 07/03/18    -$500.00
CHK 07/03/18    -$10.11
CHK 07/03/18    -$4,010.00
CC  07/04/18    -$20.94
CC  07/04/18    -$9.00
CC  07/04/18    -$10.28
CC  07/04/18    -$38.36
CC  07/05/18    -$15.95
CC  07/05/18    -$16.23
CHK 07/05/18    -$210.03
CHK 07/05/18    -$96.54
CHK 07/05/18    -$200.00
CHK 07/05/18    -$5.99
CHK 07/06/18    -$80.01
CC  07/09/18    $366.63
CC  07/09/18    -$43.13
CC  07/09/18    -$7.35
CC  07/09/18    -$54.77

I am already analyzing the raw data in several ways, including per-day aggregates from all accounts; but I would really like to view an aggregated list of all transactions.

Is this possible?
Thank you.

edit retag flag offensive close merge delete

Comments

I'd like to be able to do the same thing. I have not found an answer yet. Being new to calc I'm thinking a macro which went through each sheet specified copying all rows would work. I'll try to do that if I can't find an answer but I'm hoping something so simple would be already available...

doconnor gravatar imagedoconnor ( 2019-04-02 00:46:14 +0200 )edit