Forum Discussion
Product Count and Number Sold
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- You're welcome.
Once done, if you have questions on how to create summary reports, please come back and continue this thread (or start a new one). Assuming the data aren't confidential, if you could post a copy of the new consolidated spreadsheet on OneDrive or GoogleDrive, with a link here that grants access, that would help us help you.
- I have another question if you could help please.
I have Item SKU, Date Sold, Quantity Sold
The SKU is up to 90 different, The quantity is always 1
How do I calculate how many of each SKU I have sold?- I would create a column of unique SKU
=UNIQUE( table1[Item SKU] )
then do a countifs
=COUNTIF( table1[Item SKU] , A1#)
where A1 is the location of that UNIQUE formula You don't need to answer that prior to creating the single database covering all three years, however. The process that mtarler gives would apply after that database exists.
- WOW that works - THANK YOU very kindly for helping me.
I have created a Table with 3 years of data, how can I sort that to show data for each year instead of all 3 together?
Peter Bowen
