Creating Graphical Map of Warehouse bin locations?

[seekon]

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

 

[skywriter]

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]

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

 

[skywriter]

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

*	B	C
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*	*
*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:58px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #969696, align: center"]7A08[/TD]
[TD="bgcolor: #969696, align: center"]7A12[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]6450[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]6450[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]6450[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]6807[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]6807[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]22[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

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

*	B	C
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*	*
*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:58px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #969696, align: center"]7A08[/TD]
[TD="bgcolor: #969696, align: center"]7A12[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]6450[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]6450[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]6450[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]6807[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]6807[/TD]
[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: center"]6545[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: center"]6951[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]22[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

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]

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