I will run the macro on main stock file. Once i run the macro, it should ask me to select the sale data file. I am attaching the sample of sale data file
Essentially once the macro is run, it should do the following:-
1) Search for silimar SKU from sales data file and see if they exist in Main stock file
2) If the SKU exist, then check if "fulfillment entity"
2a) If it is "WFS fulfilled": then increase quantity in Total Sold column K by the number appearing in hte sale data file column B.
2b) Check the date stamp appearing in column column A in the sales data file.
Then increase the quantity for the same month in main stock file
For example : if the sale data says month April, then update the quantity in column M.
If the sale data says month May, then update the qunaitty in column N etc
2c) If the fulfillment entity is "seller fulfilled" then DO NOT increase the total sold column K (step 2a) above, but do increase the sale month (step 2b) above
Create another macro on main stock file, the purpose of this macro is to find the most popular selling designs within any month (the monthly sales data will come from column M (which is april), column N, which is May, column O which will be June etc
It is important to note that we need the most popular selling design (which is combined total of many different skus belong to that design). For example the first design in the file, it has sku starting from row 2 till row 9
The 3rd design is from row 18 to row 25.
So the goal is to collect the combined monthly sale of all the SKU belonging to each product, and then arrange them based on highest sale to lowest sale, in the most popular design sheet. It will be ok that each time this macro is run, a new sheet is created, so that the old "most popular design sheet" is not overwritten