Generate Monthly Sales Data from a Master List of ALL Sales

Hardware Man

New Member
Joined
Apr 10, 2013
Messages
40
Hey Guys,

I tried to work this out for myself, but since I'm posting this question here, I guess you can guess how I did! :confused:

I have 3 Sheets.

Sheet "All_Orders" has several hundred thousand entries and lists all Sales
- Product SKU
- Date
- Number of sales in the order (usually 1 sale per order but sometimes more)

Sheet "Return_Data_FBA" lists all Returns
- Product SKU
- Date
- Number of returns

On Sheet: "Costing" I have the Product SKU in column D and want to start adding my data all the way out in column FD

What I'd like to do is first determine the earliest sales date in "All_Orders".

Once I have that, I'd like for that date to be my starting point and create the following columns in "Costing".

Column D - Here, my SKU is already listed.
Column FD- Sales for the month of the SKU mentioned in column A
Column FE - Returns for the month of the SKU mentioned in column A
Column FF - Net Sales for the SKU for the month (column B minus column C)

It would then work to the right, populating the sale/returns data month by month until it reached present day.

I'd like to see the "Month/Year" in the top row for each of the above.

This may take a while to run, and that's fine for the first time. but since there are a lot of sales and a few hundred more added to "All_Orders" every day, I wouldn't want to have the macro crunch all of my old sales every time it's run (since the totals would never change anyway).

So my preference would be to save the date that the macro was last run (this little piece of data could be saved in another sheet), and then use a date of say, 2 months prior as the starting point for the date range the next time it is run.

BTW all the sales in "All_Orders" are already in chronological order, with the newest sales constantly added to the bottom of the sheet.

I know there's a lot here, but if any of you guys could at least give some pointers that would be awesome!
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top