Ideas on how to track weekly trends from multiple files

Hi,

I'm not sure how to best accomplish this weekly task, so hopefully someone has some idea(s) on how to best do this. I'm looking to track the weekly progression of how many products a buyer is managing...let me explain.

I run a distribution company and every week we get new products in and discontinue other products. We have several buyers that manage a group of products and each buyer works independently (adds some products and discontinues others). Right now, we run a weekly "overall" products report (CSV) that has everybody's products on it (see attached sample spreadsheet). Here are some comments on the spreadsheet:

- Each tab in the sample spreadsheet represents a separate CSV file that we get every week
- As you can see, from week to week, the overall product selection grows and previous products change from "Active" to "Inactive" status ("Active" means we're actively selling it and "Inactive" means that we're not actively selling it/we discontinued it)
- Every buyer changes the status of their products as needed (from Active to Inactive or back)

Here is what I want to do:

- My buyers need to always be looking for new products and I want to see is how effective/efficient my buyers are by tracking how many "Active" products they have (and how they're trending)
- Ultimately, I would love to see a chart of the past XXX weeks on one chart, but I want the easiest/fastest/best way to get that information into that chart (since each CSV file is separate)
- How do I best/easiest compile the data from multiple files (weeks) into one graph/chart that tracks the overall trends?

I hope that I explained myself well enough for you to understand. Please let me know any ideas. Thank you in advance!

Hi oeldere,

Thank you for your work. I had to do make some modifications to your pivot table/chart to fit what I'm looking for and created another pivot chart to give me a better sense of what I'm looking at (see attached spreadsheet). Overall, this was a great solution to what I'm looking to do.

Just a quick question...on your "Consolidated" tab, did you use a formula to get the data from the various tabs or just copy/paste the data directly into the list on the "Consolidated" tab?

Thank you for your help.