2kool4skool
New Member
- Joined
- Aug 18, 2009
- Messages
- 21
this is the formula=IF(ISNA(VLOOKUP((VLOOKUP(A2,_SKU1,2,FALSE)),supplier,2,FALSE)),(IF(ISNA(VLOOKUP((VLOOKUP(A2,ALT,4,FALSE)),supplier,2,FALSE)),(IF(ISNA(VLOOKUP((VLOOKUP(A2,ALT,5,FALSE)),supplier,2,FALSE)),(IF(ISNA(VLOOKUP((VLOOKUP(A2,ALT,6,FALSE)),supplier,2,FALSE)),(IF(ISNA(VLOOKUP((VLOOKUP(A2,ALT,7,FALSE)),supplier,2,FALSE)),"not found",VLOOKUP((VLOOKUP(A2,ALT,7,FALSE)),supplier,2,FALSE))),VLOOKUP((VLOOKUP(A2,ALT,6,FALSE)),supplier,2,FALSE))),VLOOKUP((VLOOKUP(A2,ALT,5,FALSE)),supplier,2,FALSE))),VLOOKUP((VLOOKUP(A2,ALT,4,FALSE)),supplier,2,FALSE))),VLOOKUP((VLOOKUP(A2,_SKU1,2,FALSE)),supplier,2,FALSE))
"SKU1" is a product name or model number.
"supplier" is a named array for a list of the SKU with corresponding inventory quantities.
"ALT" is a named array of alternate SKUs or model numbers.
we are trying to synchronize our store inventory with multiple suppliers. We purchase inventory from the suppliers in a "Just-in-time" method, so we can advertise product for sale even though we don't have it in our warehouse yet, as long as the supplier has it.
The reason for the alternate SKU list is because we assign a SKU to a product which may or may not be the same for every supplier. Multiple supplier may carry virtually identical products but they may each have a different SKU for it. As long as the SKU is in stock at at least one of the suppliers, we would consider it in stock.
this formula finds the product from our list of our own SKUs ("SKU1"), looks for a match in the supplier's inventory list ("supplier"), and returns the result as the updated quantity. If it does not find a match for our SKU in the supplier's list then it looks on the alternate sku list ("ALT") to see if there is an alternate SKU for our product that the supplier might use. The ALT array has multiple columns because there may be multiple suppliers with different SKUs for the same product or a supplier may not be consistent in what they call the product from week to week. Therfore, the Vlookup on the ALT array may be done multiple times for other columns if it does not find a match.
The formula works, but it is very long and hard to read and as you can imagine, it is very slow, even when you have a small list of SKUs to update.I would like to see if anyone has some suggestions. I know there are many ways to skin a cat in excel. I have heard of people using index/match and other things but I don't understand how they work yet. If I haven't provided enough information, let me know and I can send the actual worksheet that I use.
thanks for reading
Steve
"SKU1" is a product name or model number.
"supplier" is a named array for a list of the SKU with corresponding inventory quantities.
"ALT" is a named array of alternate SKUs or model numbers.
we are trying to synchronize our store inventory with multiple suppliers. We purchase inventory from the suppliers in a "Just-in-time" method, so we can advertise product for sale even though we don't have it in our warehouse yet, as long as the supplier has it.
The reason for the alternate SKU list is because we assign a SKU to a product which may or may not be the same for every supplier. Multiple supplier may carry virtually identical products but they may each have a different SKU for it. As long as the SKU is in stock at at least one of the suppliers, we would consider it in stock.
this formula finds the product from our list of our own SKUs ("SKU1"), looks for a match in the supplier's inventory list ("supplier"), and returns the result as the updated quantity. If it does not find a match for our SKU in the supplier's list then it looks on the alternate sku list ("ALT") to see if there is an alternate SKU for our product that the supplier might use. The ALT array has multiple columns because there may be multiple suppliers with different SKUs for the same product or a supplier may not be consistent in what they call the product from week to week. Therfore, the Vlookup on the ALT array may be done multiple times for other columns if it does not find a match.
The formula works, but it is very long and hard to read and as you can imagine, it is very slow, even when you have a small list of SKUs to update.I would like to see if anyone has some suggestions. I know there are many ways to skin a cat in excel. I have heard of people using index/match and other things but I don't understand how they work yet. If I haven't provided enough information, let me know and I can send the actual worksheet that I use.
thanks for reading
Steve
