My question is rather complicated to explain via typing it out. If I could do a bit of show-and-tell then the answer I receive will likely I be more accurate and result in less back-and-forth trying to clarify things. So it is in that spirit that I took the time to create a short video that does just that, and I ask that you watch it to get a better understanding of what it is I'm trying to accomplish. https://www.youtube.com/watch?v=G995_5QzNcE
The short text version: I need to search through a sheet with sales data on it and cross reference that to a sheet with costs of goods, then tabulate those together to determine how much (in terms of cost) inventory was sold in the sheet with sales data. There are some nuances though because the sales data doesn't have just one line per order; it sometimes has multiple lines per order that are not inventory related. See the video for a better explanation.
Thanks!
Mike
P.S. I promise it's nothing obscene, spam, or anything of the like. It truly is a "video question."
Please take a minute to read the forum rules, especially regarding cross-posting, and make sure to add links here to your posts in other forums. For example:
This is definitely a major step in the right direction! A few notes:
I think the second table you posted with the column headers MSKU, QP, Costs.Active Cost/Unit, and Multiplication is the correct concept. I'm confused by the first table.
In column A, I need a distinct list of MSKUs that sold. I pretty sure that you accomplished this already because I don't see any duplicate values in your tables and I don't see any MSKUs that didn't actually sell. Awesome!
In column B, I need a summation of the number of sales for each unique MSKU. I think your version currently counts the number of times each MSKU appears, which, on the surface, would seem to be the right approach, but actually, we need to ignore certain appearances. For example, in your tables, you have MSKU 236-B003IWR0FA-20181013-4.93 counted 3 times when it actually only sold once. It shows up 3 times in the Amazon Flat File V2 because those three rows are all part of a single order. In the first row, the customer paid $13.74 ("Principal"), but in the second and third rows, Amazon took out commissions on that sale of $4.71 and $2.06. Thus, there was only 1 sale of this item and column B for this MSKU should show 1, not 3. In Psuedo code: Sum "quantity purchased" (Column W) only when "Principal" in column N is present.
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.
