This problem has been plaguing me for months and I know there must be some way to do it, but I can't figure out how.
I have a relatively large set of data. What I am concerned with are 3 variables: a SKU, a sales order, and a quantity.
A sales order can contain any number of skus in any quantity.
Given a set of SKUs and quantities I want to be able to pull the sales order numbers that would associate with that quantity of skus.
For example:
I have a quantity of 15 of SKU1.
And I have the following sales orders that include SKU1: SO1 SO2 SO3 SO4 SO5
The quantity of SKU1 in each SO is 8, 3, 4, 2, 6
I therefore want to show that the amount of SKU1 I have will fulfill sales orders SO1, SO2, and SO3.
Is there any way to quickly do this?
Some things I have tried:
-a pivot table that shows all the sales orders a sku can be found in and in what quantities. This is ok, but still requires a significant amount of time to manually drill down into.
-"exploding" out my skus by "multiplying" the SO by the quantity needed so that each SO represents 1 unit. I used a gigantic formula that crashed excel. This was that formula =IF(ROWS(S$2:S2)>SUM(K$2:K2),"",LOOKUP(ROWS(S$2:S2)-1,SUBTOTAL(9,OFFSET(K$1,0,0,ROW(K$2:K2)-ROW(K$2)+1)),D$2:D2))
Hopefully this makes sense. I can try to clarify further if anyone thinks they know how to help.
