SOLVED

I found the limit of my Excel knowledge, can I ask for some help?

Copper Contributor
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.
3 Replies
It's difficult, if not impossible, to imagine a suggested formula for you unless you attach your sample file with the manually entered results that you want to be returned by formulas.

@Twifoo 

Thank you for the reply, you are right. I am uploading the workbook now (apologies in advance for the style sheet, but I am dyslectic and the colours help me to quickly remind me the block of text on the tables and which columns have linked, calculated, output cells, etc.).

 

Anyway, I did unhide the columns to show the calculated cells on main sheet(tool) 'Wiz', Unfortunately due to time constraints I wasn't able to document/commented the workbook too much, but I am eager to start working on the issue, so I hope the sheet layout is fairly clear to understand.  

 

Additional remarks:

I want to use the 'RESINV' sheet to validate if there is inventory and which 'slot' is on before allocating a pickface; hoping there is one available location closest to the reserve.

 

Thank you again.

 

best response confirmed by NEXTIA (Copper Contributor)
Solution
I am closing this post, I guess was the wrong place, maybe not enough experts, not enough users or my post was unreadable; either way I found the solution by myself, so not the end of my Excel knowledge after all. Thank you for those who took the time to read it I guess.
1 best response

Accepted Solutions
best response confirmed by NEXTIA (Copper Contributor)
Solution
I am closing this post, I guess was the wrong place, maybe not enough experts, not enough users or my post was unreadable; either way I found the solution by myself, so not the end of my Excel knowledge after all. Thank you for those who took the time to read it I guess.

View solution in original post