"Stacking" specified columns, into one column function?

[czcastermaster]

Hi,

I'm trying to create a spending tracker for all our credit cards and accounts. They all export as .csv's with the following essential data: date, description, amount, category

Every month I'd like to create a new sheet, paste all the above data into it, and have it spit out a category summary.

I don't know VBA well but my solution if I did would be to somehow "stack" columns that I specify.

I have attached a spreadsheet that kind of demonstrates what I am thinking of. Any ideas?

In my search I did find something similar but it doesn't seem to work for the data format I have...
https://chandoo.org/forum/threads/consolidate-columns-with-the-same-headers.13051/

 

[vletm]

czcastermaster
Could You use something like this rough sample?
You could copy correct columns as this sample.
You could get Your expected results with Pivot-table.
You wrote something about Every month I'd like to create a new sheet ... would You use one sheet?
Notes:
Your sample data should be more realistic.
eg Dates should be dates and amounts should be numbers and so on.

 

[p45cal]

I suspect this could be handled best with Power Query.
Could you attach some sample csv files from different suppliers so we can get an idea of what the raw data looks like?
These files should never have been opened or even touched by Excel! (If there are privacy problems, start a Private Conversation with me.)
I'd hope for you to be able to put these csv files in a location(s) and get a summary, perhaps without any vba code at all.

 

[czcastermaster]

OK, I downloaded some sample data and cleaned it up for posting here.

Hopefully this spreadsheet makes more sense with what I'm trying to do overall. Each month's spending gets compiled into it's own sheet, and I can use that data elsewhere.

What is not shown is that for each month, the actual number of accounts between myself and my wife is like 8... a few cards+checking accounts. Copying and pasting takes a couple of minutes but I'd like to automate the next step of sorting and stacking the data into one group as shown in grey in sheet "12_22".

 

[vletm]

czcastermaster
I give up this cause still monthly sheets,
... but if You'll change Your idea, then let me know.
You seems to missed those sample .csv-files.

 

[czcastermaster]

Oops, here it is.

And I'm not opposed to tracking everything on one sheet, in fact I tracked everything on one sheet previously, when I only had one card I spent on. I used long complicated and laggy INDEX functions to parse out data for each month. However now with multiple datasets I need to combine them I think, in order to analyze. Which is why I thought of a stacking functionality... This is just a way I thought of to get it done, not saying it has to be this way

I am not familiar with power query

 

[vletm]

czcastermaster
Have You try to use Pivot-table?
... did You check that my sample?
... then You could skip those formulas.

 

[p45cal]

In the AC1.csv type file, it it correct that there is no year data in the Posting Date column?
Perhaps the name of the file contains a year clue?

What I'm in the process of doing is processing the different types of file in different ways so that they can be merged successfully. I'll be asking you to put multiple files of the same type into their own folder (each month you just add a new file to the existing ones), then Power Query will load all the files in finds in those folders, tweak the headers and merge them. So the problem with year data is already important as we are January 2023 and the data is 2022.

 

[czcastermaster]

In the AC1.csv type file, it it correct that there is no year data in the Posting Date column?
Perhaps the name of the file contains a year clue?

What I'm in the process of doing is processing the different types of file in different ways so that they can be merged successfully. I'll be asking you to put multiple files of the same type into their own folder (each month you just add a new file to the existing ones), then Power Query will load all the files in finds in those folders, tweak the headers and merge them. So the problem with year data is already important as we are January 2023 and the data is 2022.

Hi p45cal, there is year data when the individual cell is examined, for whatever reason the formatting defaulted to day/month in excel, I have changed this and attached.

All the data from the account exports have day/month/year in their equivalent "date" column, however it's labeled.

 

[czcastermaster]

czcastermaster
Have You try to use Pivot-table?
... did You check that my sample?
... then You could skip those formulas.

Hi, yes I understand pivot tables can be used to organize the data, however my concern here is how to automate (to a degree) aggregation of several sources of similar datasets that have differing column headings and placements. In your example, you seemed to have copied and pasted accounts 2 and 3 underneath account 1 prior to generating the pivot table. The question of this thread is how to automate this data aggregation process, as opposed to highlighting selections and copying/pasting.

 

[p45cal]

It looks like you're messing with the data in Excel and then saving as a csv file. I did ask to see raw csv files (unadulterated by Excel) for this very reason.
For example, in the latest attached csv file it looks like you know what you're spending 12 months in the future!
I want to see raw csv files.

FYI this is where I've got to so far (years are wrong still (and dates are showing as d/m/y but they're proper Excel dates; if you're in a m/d/y locale they'll show that way)):


There is no other table in the workbook, Power Query looks at the csv files in the folders, and updates itself from them.

 

[czcastermaster]

Sample dates should've been 2022 like attached.

I do have privacy concerns sharing raw csvs on the internet

 

[p45cal]

do have privacy concerns sharing raw csvs on the internet

which is why I suggested starting a Private Conversation here.

 

[vletm]

czcastermaster
From: I'm trying to create a spending tracker for all our credit cards and accounts.
To: The question of this thread is how to automate this data aggregation process, as opposed to highlighting selections and copying/pasting.
Many things are possible after You could give needed useful details.
My the 1st sample was based Your given the 1st file ... it was a sample, which gave same results. Even that is possible to automate.
There will be some challenges eg,
... How to take care Your headers? - if same data has many different header names.
... How to take care duplications? - if some file has 'loaded' more one once.
... I would like to see realistic csv-files. Those data could be even like in Your #1 posting... except Your expected results.