Creating Graphical Map of Warehouse bin locations?

seekon

New Member
Joined
Aug 19, 2007
Messages
14
Hi Guys,

I am not that skilled in Excel VBA, and have been asked to help create a map of all the skus that are located in lanes (bins) in a warehouse. The data is being generated into a CVS file from another database. What I need to do is take this data and sort it into columns visually looking like the physical warehouse. The purpose is to see how full the warehouse is and what is in each bin without walking into the warehouse.

I have started with a simple layout, and the data, but I am unfamiliar on how to bring the data inside the columns via formulas.

http://sekhon.ca/downloads/projects/warehouse/WarehouseControl1.xlsx

Any help would be greatly appreciated :)
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
You spreadsheet has a lot of data, you should be more specific as to what you ultimately would like to see. Giving an example referencing your spreadsheet would be helpful.
 

seekon

New Member
Joined
Aug 19, 2007
Messages
14
I would like to have all the partcodes displayed underneath each of the designated Bins, with a count at the bottom. This way I know what partcode is in the bin, and how many partcodes are in this bin. This way I can get a visual of how much room I have left in the bin, each bin is the same size.

I just updated this to be a bit more clear, can you please have another look, thanks :)

http://sekhon.ca/downloads/projects/warehouse/WarehouseControl1.xlsx
 
Last edited:

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Okay we have a couple of issues. I am using a formula that looks at the value in B4 and tries to match it with your locations in column E of your Data sheet. I was getting errors and couldn't figure out why. I finally discovered that your bin locations in column E of your Data sheet have multiple hidden spaces in them. I had to remove these manually, I don't know if they are a by product of you importing your data from somewhere else, but you need to be aware of it. Excel sees a number with no spaces and what appears to be the same number with spaces after the number as being different. If this list is going to be updated often you might want to consider a macro that fixes the issue. What I did was entered a column to the right of E and used a trim formula. So in the blank column F I entered in cell F4 the formula =Trim(E4) and copied it down, which brings the value of E values into F without the spaces, but it's a formula that looks at E, so I have to copy all the F cells and then "paste special values" back onto themselves. I then deleted the original column E and the column F becomes column E and all the bins are now free of spaces.

The second issue is your sheet goes to row 29 and that's not going to be enough for some of your locations, you need to add some rows.

Note the formula in Cell B29 is the formula I want you to use at the bottom of your data to count, this is very important because this formula is reference in the formula in cell B5 in my example. So add your extra rows to fit your data and enter the formula shown in B29 of my example in the last row and make sure you change the references in the formula in cell B5 to whatever that last row is, taking care to preserve the dollar sign.

The reason I entered the formula in B5 the way I did is for timing purposes. You have a lot of data and this will keep the calculation time down.

The formulas are both array formulas and you must use Control Shift Enter to confirm the formulas, but once you have done this you can just copy the formulas to all the other cells.

WarehouseControl1

*BC
47A087A12
564506545
664506545
764506545
868076545
968076545
10*6545
11*6545
12*6545
13*6545
14*6545
15*6545
16*6545
17*6951
18*6951
19*6951
20*6951
21*6951
22*6951
23*6951
24*6951
25*6951
26*6951
27**
28**
29522

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:58px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B5{=IF(ROWS(B$5:B5)>B$29,"",INDEX(Data!$A$1:$A$7710,SMALL(IF(Data!$E$1:$E$7710=WarehouseControl1!B$4,ROW($E$1:$E$7710)),ROWS(B$5:B5))))}
B29=COUNTIF(Data!$E$2:$E$7710,WarehouseControl1!B$4)

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:

seekon

New Member
Joined
Aug 19, 2007
Messages
14
Thanks a ton Skywriter.

Would you be kind enough and email me your version of this sheet, seekon@gmail.com ? This will allow me to compare why I am having problems using the formulas.

Thanks again :)

Okay we have a couple of issues. I am using a formula that looks at the value in B4 and tries to match it with your locations in column E of your Data sheet. I was getting errors and couldn't figure out why. I finally discovered that your bin locations in column E of your Data sheet have multiple hidden spaces in them. I had to remove these manually, I don't know if they are a by product of you importing your data from somewhere else, but you need to be aware of it. Excel sees a number with no spaces and what appears to be the same number with spaces after the number as being different. If this list is going to be updated often you might want to consider a macro that fixes the issue. What I did was entered a column to the right of E and used a trim formula. So in the blank column F I entered in cell F4 the formula =Trim(E4) and copied it down, which brings the value of E values into F without the spaces, but it's a formula that looks at E, so I have to copy all the F cells and then "paste special values" back onto themselves. I then deleted the original column E and the column F becomes column E and all the bins are now free of spaces.

The second issue is your sheet goes to row 29 and that's not going to be enough for some of your locations, you need to add some rows.

Note the formula in Cell B29 is the formula I want you to use at the bottom of your data to count, this is very important because this formula is reference in the formula in cell B5 in my example. So add your extra rows to fit your data and enter the formula shown in B29 of my example in the last row and make sure you change the references in the formula in cell B5 to whatever that last row is, taking care to preserve the dollar sign.

The reason I entered the formula in B5 the way I did is for timing purposes. You have a lot of data and this will keep the calculation time down.

The formulas are both array formulas and you must use Control Shift Enter to confirm the formulas, but once you have done this you can just copy the formulas to all the other cells.

WarehouseControl1

*BC
47A087A12
564506545
664506545
764506545
868076545
968076545
10*6545
11*6545
12*6545
13*6545
14*6545
15*6545
16*6545
17*6951
18*6951
19*6951
20*6951
21*6951
22*6951
23*6951
24*6951
25*6951
26*6951
27**
28**
29522

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:58px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B5{=IF(ROWS(B$5:B5)>B$29,"",INDEX(Data!$A$1:$A$7710,SMALL(IF(Data!$E$1:$E$7710=WarehouseControl1!B$4,ROW($E$1:$E$7710)),ROWS(B$5:B5))))}
B29=COUNTIF(Data!$E$2:$E$7710,WarehouseControl1!B$4)

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

seekon

New Member
Joined
Aug 19, 2007
Messages
14
Thanks again Skywriter for sending me excel file. I have one additional request if possible. Is there away to modify the formulas so that the items are sorted by date, with oldest being on the top, and newest being in the bottom. This way I would know where in the row the incorrect product is binned, sometimes people bin the product in the wrong bin. Looking at this visual map, and with these columns being sorted by date, we would be able to see how far the incorrect product is in the row. The date is referenced in the data tab, but not sure how easy it is to reference via forumla already setup.

I am going to place conditional formatting on so that it is easy to see all the same items within different bins, I wish excel had more colour formatting choices already setup so you don't have to customize each one, it's going to take awhile.

Thank you for all your efforts :)
 

seekon

New Member
Joined
Aug 19, 2007
Messages
14
Oops, I have another question for you, due to the product not being binned correctly, the items in on particular bin flows over the amount of room that is allowed in the columns there passed the countif cells. I have placed the count at the top, but when I attempt to remove the count at the bottom, I end up messing up forumlas, which then no longer shows the data, I do recall you mentioning that it matters where the count columns are. When you get a minute, can you please have a look, and adjust the formulas so that the count is displayed only at the top and the bottom is removed, if this is possible.

http://sekhon.ca/downloads/projects/warehouse/WarehouseControl1-2.xlsx

Thanks again, my email is seekon@gmail.com
 

Watch MrExcel Video

Forum statistics

Threads
1,095,289
Messages
5,443,592
Members
405,240
Latest member
spl

This Week's Hot Topics

Top