Hi everyone! Hope you’re enjoying your weekend.
I’m struggling with something I believe should be easy to achieve.
SITUATION: I have a spreadsheet with a list of sales (date, product, subproduct, price) and I want to bring as a new column, what was the price at the time the sale was done
WHAT DO I HAVE:
1) Sold products: List of all products sold - note that some products (e.g., socks) don’t have a subproduct variation (e.g., color)
2) List of costs: As you can see, the same product shows up more than once. That happens when a supplier updates the cost. For example, the cost for the T-Shirts was $6 from 1/1/23 up until 1/14/23, when it was updated to $9
3) Desired output: Add two columns to the original spreadsheet. In the first column, bring the cost of the product-subproduct combination that applies to the time of sale (should be the closest price available in the past). In the second column, just add a flag if the product wasn’t found in the cost database.
Any help or suggestion is HIGHLY HIGHLY appreciated.
Thanks you all!