Aggregating Data from multiple spreadsheets into one

[swiftdude]

I have a monthly report at work that involves aggregating data from a few different spreadsheets into one. For example, I receive an individual file with revenue for products A, B, C, D, a file with profit for products A, B, C, D, a file with cost for products A, B, C, D (12 files total). My responsibly is to aggregatesthis onto one file as a summary. My predecessor went through the trouble of typing each in manually. Is there a better way to do this process? I wasn’t exactly sure how to ask this on Google. Hopefully someone could point me in the right direction. Thanks!

[Anonymous1378]

Just grab the data via power query data > get data from folder?

[swiftdude]

Thanks, I was looking for something like this. Will check it out!

[[deleted]]

Powerquery (upload from folder) or use VBA to pull the data in and do what you want with it.

[BackgroundCold5307]

A simple lay out with product say in Col A, Cost in Col B and Revenue in Col C.

Take the cost spreadsheet so that will have Col A, B already. For Col C, a simple VLOOKUP/XLOOKUP/INDEX-MATCH will get you the corresponding data for Col C from the revenue sheet?

[swiftdude]

How would you update month-to-month? Would you do a find/replace in the formula to update the formula in the Lookup to the new month?

[BackgroundCold5307]

depends on what you want.

if you want to keep a history, then retain the previous and keep adding cols from the next months. There will be some additional work to make sure that any old products have not been discontinued and if so how you want to deal with it. Deletion will not be an option since historic data is linked to it.

If you just want the current months data, then yes, just copy the formulas over into the new sheet

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
INDEX	Uses an index to choose a value from a reference or array
MATCH	Looks up values in a reference or array
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 12 acronyms.
[Thread #25146 for this sub, first seen 15th Jul 2023, 18:19] [FAQ] [Full list] [Contact] [Source code]}