Forum Discussion
Product Count and Number Sold
I just want to underscore what mtarler has already said. You would be way ahead if you were to work with a single data table showing:
date, item, quantity, etc.
from that single database you could create a pivot table that shows data by month, quarter, year
Excel can do wonders with single well designed data tables. We humans like to break things out by month (or other sub-divisions) because it's clearer to our eyes and minds. But don't impose that structure on Excel; let it do the breaking out after you have collected ALL the raw data into a single table.
How do I create a data table from my existing workbook which has 3 years of data in separate worksheets
How do I create a data table from my existing workbook which has 3 years of data in separate worksheets?
Probably most effectively done (unless there's a Power Query way to do it, which isn't a method I know)...by a series of copy/paste operations. This is assuming that the data in each sheet are arrayed in the same way. You'll need to add a column representing dates (months and years if that's the level of granularity you employ), but other than that just make sure you're putting each row from each month's sheet into the appropriate columns. Three years would have 36 months; so copying and pasting, adding dates, will take a bit of time, but not a lot, especially in view of the time it'll save once done.
- Thank you very kindly
Peter Bowen
