Consolidating multisheet data

[mtmconsult]

This is my first time using this forum and hoping someone can help :crossed_fingers: I'm using Microsoft Excel for Mac ver. 16.44 and, Im not a proficient/expert in the use of Excel. Problem: I want to combined the data from several (12 individual months) sheets/tab/formatted tables into a summary sheet, so can then create a single pivot table on the resulting summary data. I have found the 'consolidate' function but it's not very user friendly in selecting more than one 'table' and if I select the ranges therein the results are very messy and of no use.

Your help would be appreciated.

Best wishes 7 stay safe. 

[#2]

@mtmconsult 

Well, one learning here certainly is to stop storing data for each month on a separate sheet. Excel is great at breaking a single database into parts, based on any number of variables, but definitely including months....and producing Pivot Tables that report on that consolidated data.

You don't say whether or not the different months all have essentially the same layout. Presuming they do, AND presuming that it's basically a tabular data set--rows and columns that record, say, daily transactions of money in and out, or some other comparably consistent kind of data....is there anything stopping you from just doing a manual consolidation and then moving forward with that single database?

That truly would be the best way forward. If the data are already tabular, it may be a pain but it's short-lived.

FWIW, I just finished incorporating the year=end data from my financial institution (which comes as a single spreadsheet) into the consolidated data of prior year-end data. It involves copying and pasting into a single table.

I'm sure that Power Query can do this kind of thing, but you and I on our Macs are pretty much out of luck--that PQ functionality is only on the Windows systems.

[#3]

@mathetes 

Many thx for coming back to me on this, and ... yes, I suspect your proposal of 'manually' consolidating i.e. copy&paste each months sheet into one summary sheet may very well be the answer. The reason I have one sheet for each month is that the CC bank only gives the option to download statements monthly - but I will double check if there's a single sheet (yearly/time-bound) dbase option :crossed_fingers:. Yes, if I can get all on one sheet I think my skills are sufficient to the create a single Pivot.

I did do a little research on the Power Query tool but as alluded too it doesn't seem to appear as a, 'add-on' in Apples download store.

Thx again and stay safe.

[#4]

@mtmconsult 

You wrote: The reason I have one sheet for each month is that the CC bank only gives the option to download statements monthly - but I will double check if there's a single sheet (yearly/time-bound) dbase option .

Even if there's not from the bank a single annual file, you still can do the copy and paste of monthly sheets and make your own single, consolidated. That would also enable you to do some year-to-year comparisons over time. But also, since what you're doing is looking at your personal banking transactions, you probably would desire to get the more timely data that monthly downloads would provide.

Yes, if I can get all on one sheet I think my skills are sufficient to the create a single Pivot.

I do this, as I think I mentioned, not just from my (checking account) bank, but also from four different credit cards, every month. They're each organized slightly differently-- some more columns than others, etc.--so I have to do some editing before combining them all into one consolidated database, but once that's done, the Pivot Table is easy.

You might also be able to use the Money-in-Excel master template that Microsoft has recently made available. My own bank uses dual-factor authentication, which precludes my using it, but it looks to have the kind of functionality--including linking with many financial institutions. https://templates.office.com/en-us/money-in-excel-tm77948210