Greetings,
Thanks, in advance to those who might be able to help. I'll make it as concise as I can while providing context when needed.
Scenario:
I work for a Distribution Centre with about 10,000 unique location in the warehouse (4 different sizes and storage capacity [full, half, quarter, and reserve]) from where order pickers collect and drivers replenish stock from stock reserves or just delivered to the inbound office.
Background:
I built an Excel workbook that uses sheets working as filters, and format the data into tables from where a series of formulas provide the user with an available location appropriate for the box size, stock quantity and gender (as the warehouse stores clothing). It is working relatively well considering the complexity, but I need to polish it and make one major improvement for it to be easy to work with.
Let me explain. I must allocate one location (a.k.a. Pickface) a day before fulfilling the orders so the forklift driver can put the stock from the reserves or goods-in area.
The problem:
Currently, the formulas provide me with a unique location. However, the pickface is provided in alphabetic order (no specific reason for the sorting choice, so it can change if the solution offered requires it) and this has a major impact on drivers' performance because sometimes they must travel from end to end of the warehouse (from reserve to pickface location) to replenish stock. I want to find a way for Excel to use another table where I have the reserve location(s) and provide the end user with the closest pickface available to the reserve location and not just the next one available from the list; unless the stock is on the inbound platform.
The formula:
Selection of unique location from the filtered table
=IF(AND($StockType="B",$LocationType="Full"),INDEX(FilteredLocationTypeFull, RANK.EQ(FilteredLocationTypeFull!D3, FilteredLocationTypeFull!D$3:D$1400,1) + COUNTIF(FilteredLocationTypeFull!$D$3:D3, FilteredLocationTypeFull!D3), 1) <== Repeats for all permutation of StockType & LocationType; a total of 5, nested on the same "IF" statement.
*"B" first letter of the stock group (B=Bulk, S=Singles)
Attempted solution:
I have tried using the vlookup function with the last parameter value of '1' which gives you the approximate match but that did not work as that makes the tool giving duplicate values.
Remarks:
Constraints
- The solution must be in Excel due to end users' lack of training on other PC software. However, I have strong foundations on VBA so if there is a script I can use, feel free to mention it.
- Using Excel 2010 without power query installed.
Any pointers on how to resolve it would be appreciated.
P.S. How do I upload my workbook?, it could be easier for reviewing and testing, maybe?
Thanks, in advance to those who might be able to help. I'll make it as concise as I can while providing context when needed.
Scenario:
I work for a Distribution Centre with about 10,000 unique location in the warehouse (4 different sizes and storage capacity [full, half, quarter, and reserve]) from where order pickers collect and drivers replenish stock from stock reserves or just delivered to the inbound office.
Background:
I built an Excel workbook that uses sheets working as filters, and format the data into tables from where a series of formulas provide the user with an available location appropriate for the box size, stock quantity and gender (as the warehouse stores clothing). It is working relatively well considering the complexity, but I need to polish it and make one major improvement for it to be easy to work with.
Let me explain. I must allocate one location (a.k.a. Pickface) a day before fulfilling the orders so the forklift driver can put the stock from the reserves or goods-in area.
The problem:
Currently, the formulas provide me with a unique location. However, the pickface is provided in alphabetic order (no specific reason for the sorting choice, so it can change if the solution offered requires it) and this has a major impact on drivers' performance because sometimes they must travel from end to end of the warehouse (from reserve to pickface location) to replenish stock. I want to find a way for Excel to use another table where I have the reserve location(s) and provide the end user with the closest pickface available to the reserve location and not just the next one available from the list; unless the stock is on the inbound platform.
The formula:
Selection of unique location from the filtered table
=IF(AND($StockType="B",$LocationType="Full"),INDEX(FilteredLocationTypeFull, RANK.EQ(FilteredLocationTypeFull!D3, FilteredLocationTypeFull!D$3:D$1400,1) + COUNTIF(FilteredLocationTypeFull!$D$3:D3, FilteredLocationTypeFull!D3), 1) <== Repeats for all permutation of StockType & LocationType; a total of 5, nested on the same "IF" statement.
*"B" first letter of the stock group (B=Bulk, S=Singles)
Attempted solution:
I have tried using the vlookup function with the last parameter value of '1' which gives you the approximate match but that did not work as that makes the tool giving duplicate values.
Remarks:
Constraints
- The solution must be in Excel due to end users' lack of training on other PC software. However, I have strong foundations on VBA so if there is a script I can use, feel free to mention it.
- Using Excel 2010 without power query installed.
Any pointers on how to resolve it would be appreciated.
P.S. How do I upload my workbook?, it could be easier for reviewing and testing, maybe?
