Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Creating Graphical Map of Warehouse bin locations?

  1. #1
    New Member
    Join Date
    Aug 2007
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Creating Graphical Map of Warehouse bin locations?

    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/...eControl1.xlsx

    Any help would be greatly appreciated

  2. #2
    Board Regular skywriter's Avatar
    Join Date
    Feb 2014
    Posts
    1,642
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Graphical Map of Warehouse bin locations?

    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.
    Bruce
    ______________________________________________________
    There is no better way to learn than by doing.

    - Windows 7 -- Excel, Access & Word 2010
    - Post your spreadsheet online: Dropbox
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Aug 2007
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Graphical Map of Warehouse bin locations?

    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/...eControl1.xlsx
    Last edited by seekon; Jul 10th, 2014 at 02:18 AM.

  4. #4
    Board Regular skywriter's Avatar
    Join Date
    Feb 2014
    Posts
    1,642
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Graphical Map of Warehouse bin locations?

    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
    4 7A08 7A12
    5 6450 6545
    6 6450 6545
    7 6450 6545
    8 6807 6545
    9 6807 6545
    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 * *
    29 5 22

    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)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by skywriter; Jul 10th, 2014 at 05:07 AM.
    Bruce
    ______________________________________________________
    There is no better way to learn than by doing.

    - Windows 7 -- Excel, Access & Word 2010
    - Post your spreadsheet online: Dropbox
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Aug 2007
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Graphical Map of Warehouse bin locations?

    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

    Quote Originally Posted by skywriter View Post
    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
    4 7A08 7A12
    5 6450 6545
    6 6450 6545
    7 6450 6545
    8 6807 6545
    9 6807 6545
    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 * *
    29 5 22

    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)
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  6. #6
    New Member
    Join Date
    Aug 2007
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Graphical Map of Warehouse bin locations?

    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

  7. #7
    New Member
    Join Date
    Aug 2007
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Creating Graphical Map of Warehouse bin locations?

    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/...ontrol1-2.xlsx

    Thanks again, my email is seekon@gmail.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •