Create Pareto chart from multiple Excel workbooks

New Contributor

A bit of background - I work as a data analyst for a manufacturing company. We are starting daily safety inspection checklists (things like PPE use, proper chemical storage, etc) for each area of a plant (weld shop, paint, etc).

 

I have a workbook setup with tabs for Mon-Fri and a sixth tab which totals the daily counts based on different categories and then a Pareto chart is built from that.

 

Theoretically I would have one Excel file per week from each department at a given plant. How would I create a plant-wide chart which combines the totals from each department workbook? I'd prefer to use just formulas or something like Power Query and stay away from VBA or scripting. Any thoughts or links to a tutorial would be amazing.

4 Replies
If you're going the PowerQuery route:
https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query...

I think vba might be the better solution for this situation, A macro could be written to open selected workbooks in a folder, fetch the needed data and return it to your workbook for the charts. PowerQuery is going to have some overhead with those queries.
Interesting, I'll check out the link.

I've never done much with macros or VBA which is why I'm a little hesitant to go that route (although I do other kinds of programming sometimes). Where would I start with that?
The way I see it, you'd have two subroutines:
1. Open multiple workbooks from a given folder. The code simply opens each workbook, performs the actions in macro #2, then closes the workbook.

2. Locate the data to be extracted from each sheet of each workbook and then return it to your active workbook. This step can be fairly straightforward if there's consistency in where the data resides in each workbook.
On the sixth worksheet there are two columns: a column of inspection category descriptions and a column of cells which contain formulas that add up the tallies from the Monday-Friday sheets, which are also very uniform. Beside those is the Pareto chart which just visualizes the two columns. Theoretically, unless the person filling out the sheet changes something, each workbook should be setup exactly the same besides a few cells at the top for the plant and department and staff information.

That said, would you be able to point me to a good VBA tutorial? I know there are dozens on YouTube and around the internet, but I'd like to get this working ASAP and don't want to waste time on tutorials that don't cover relevant information. Thanks for your patience!