Product Count and Number Sold

[PeterBowen]

I have a series of Excel worksheets in a single workbook that I create every month to track sales of products so I have columns for ITEM purchased and QUANTITY which is always one. There are up to 90 different items but the quantity sold is always 1.

1. How can I combine all of the worksheets into a single workbook for each year?

2. I would like to know how to see how many of each item I have sold?

Peter Bowen

[#2]

why not CREATE them in 1 worksheet to start with? Then if you want to see just 1 month you can just filter by that month. I would recommend a column for date to start and to 'Format as a Table' (from the home menu). Once all the data is in 1 table in addition to filters you can use pivot tables or other functions to break the data out by month, or item or customer or whatever you want. Basically it is easier to start with a single data source than having man y fragmented sources.

[#3]

@PeterBowen 

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.

[#4]

@mathetes 

How do I create a data table from my existing workbook which has 3 years of data in separate worksheets

[#5]

@PeterBowen 

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.

[#6]

Thank you very kindly

Peter Bowen

[#7]

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.

[#8]

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?

[#9]

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

[#10]

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.

[#11]

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

[#12]

i would recommend you 'Format as a Table' (Home -> Styles -> Format as Table)
This will default to adding the drop down quick filters that make it easy to sort and filter the set of data. You could just add those quick filters (Data -> Filter) but by formatting as a Table does more. For example when you select a cell in the table you have the Table menu and you can change the name of your Table. And you can reference all the data in the table using that name. So if you call it DATA and the first column is Date then DATA[Date] will refer to that date column.

[#13]

@PeterBowen 

In addition to the suggestions by @mtarler for sifting and sorting after making this data into an official "Excel Table," there are within Excel multiple ways to extract data for reporting by year (or other time periods). I mentioned the Pivot Table in an earlier post; that is a technique that many find VERY valuable for summary reporting. There are also many YouTube videos that show how to use Pivot Tables.

If the data you are working with are not confidential, you could post a copy on OneDrive or GoogleDrive, with a link pasted here that grants access. Then @mtarler or I could offer more specific suggestions.