I'm struggling with a complex problem and hoping someone can help me find a simple automated solution.
Currently, we export daily usage data of products and UPCs from our POS system into an Excel file at our retail store.
Using this file, I want to consolidate identical products (UPCs) and add up their quantities sold, similar to power queries. I also need to retain the stock size of each item with its UPC.
While I can do this manually, I need to take it further. Let's say we sell items in large stock sizes, like Product A in boxes of 50 and Product B in boxes of 100. If we sell 30 of Product A and 120 of Product B, I want to generate a reorder for 1 box of Product B and retain the partial quantities for the next run.
The resulting table would be dynamic, showing Product A hasn't met the threshold with 30 sold and Product B showing 20 sold (since we reordered the other 100).
The next day, I export the same file and update the virtual quantities with the new ones. So, if we sold 120 of Product A and 80 of Product B, the system would order 3 boxes of Product A (30 + 120 = 150 / 50 = 3) and 1 box of Product B (20 + 80 = 100 / 100 = 1). The new virtual quantities would show both products at 0.
I'm not sure if this is possible, but I know Excel and power queries can handle the data work. I'm hoping someone has a great idea for retaining the virtual inventory and generating orders!