+ Reply to Thread
Results 1 to 10 of 10

Create a dynamic list based on multiple dynamic factors.

  1. #1
    Registered User
    Join Date
    02-25-2014
    Location
    Grand Rapids, MI
    MS-Off Ver
    Excel 2013
    Posts
    26

    Create a dynamic list based on multiple dynamic factors.

    Hey guys,
    You've solved my serious headaches in the past I'm hoping you can do it again. I spent hours trying and only have a swollen brain to show for it. Basically, I need to somehow create a list that can pull enough top ranking items to fill a given available space given keeping in consideration the different amount of space each item requires. Please see the attached file for more details.
    Thanks,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Create a dynamic list based on multiple dynamic factors.

    Should the highest ranking ones go in first? What was the criteria for selecting the ones you have filled in?
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Create a dynamic list based on multiple dynamic factors.

    How come some HALF take up two spaces?

  4. #4
    Registered User
    Join Date
    02-25-2014
    Location
    Grand Rapids, MI
    MS-Off Ver
    Excel 2013
    Posts
    26
    Quote Originally Posted by Jacc View Post
    How come some HALF take up two spaces?
    A HALF can't be in the same bin as a FULL therefore depending on where the item is in the order of items a HALF may need to be in a bin by itself. For example, if there are three HALF 's in a row and one FULL then two HALF's would share a bin, one HALF would have it's own bin and the FULL would have it's own bin, if there are four HALF 's in a row and a FULL then it still uses only three bins.

  5. #5
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Create a dynamic list based on multiple dynamic factors.

    The product list, as you say, can change. However, you seem to have arranged the products into their bins by order of the product list (i.e. from top to bottom). The way I see it, depending on how you arrange the product list you have a different solution each time, even with the same items) just because of how the HALFs and FULLs are arranged from top to bottom.

    Is this list fixed the way it is now, or can they be re-arranged as long as it fits the 72 bins you require?

    The reason why I'm asking this is because the only way I can see it done using formulas would be to re-arrange the list according to their ranking.

  6. #6
    Registered User
    Join Date
    02-25-2014
    Location
    Grand Rapids, MI
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Create a dynamic list based on multiple dynamic factors.

    If you look at the the description of the items you'll see that they are actually sizes of product. This product list will later be represented as part of a display. The problem with shuffling them into the order of their rank is that the bin configuration will go out of wack. For example if you had six items in order of description that required FULL, HALF, FULL, HALF, HALF, HALF it would require five full bins because the first HALF would need to be by itself and the next two HALF's could share a bin while the last HALF would also be in a bin by itself. However, if the same products were reorganized by rank and it instead laid out as HALF, HALF, HALF, HALF FULL, FULL, for example, it would only need four drawers which wouldn't be the correct number of bins because the products have to be laid out in the order of their description size.

    The purpose of this program that I'm working on is to fit in as many of the top performing items that fit into a given display size. So if I have only 72 available spaces (36 bins) then I have to make sure I put in as many of the highest ranking items as possible given the available space. Please keep in mind that the finished product does not have to be accomplished in one step. It can be as many steps needed to accomplish the task.

  7. #7
    Registered User
    Join Date
    02-25-2014
    Location
    Grand Rapids, MI
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Create a dynamic list based on multiple dynamic factors.

    yes, it needs to keep in consideration the highest ranking items

  8. #8
    Valued Forum Contributor quekbc's Avatar
    Join Date
    01-18-2010
    Location
    Sydney, Australia
    MS-Off Ver
    2010, 2013, 2016
    Posts
    1,149

    Re: Create a dynamic list based on multiple dynamic factors.

    Quote Originally Posted by quekbc View Post
    Is this list fixed the way it is now, or can they be re-arranged as long as it fits the 72 bins you require?
    The reason why I'm asking this is because the only way I can see it done using formulas would be to re-arrange the list according to their ranking.
    Since I've not received a response on this, I'm just gonna post a possible solution here after sorting the product list by rank. (or see attached file)

    Row\Column
    I
    J
    K
    L
    M
    4 Finished List
    5 Item# Description Rank Configuration
    6 40015 #6 x 3/4 1 HALF 1
    7 40060 #8 x 3/4 2 HALF 2

    Formula in I, J and L7
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula in K7
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula in M7
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-25-2014
    Location
    Grand Rapids, MI
    MS-Off Ver
    Excel 2013
    Posts
    26

    Re: Create a dynamic list based on multiple dynamic factors.

    Quote Originally Posted by quekbc View Post
    Since I've not received a response on this, I'm just gonna post a possible solution here after sorting the product list by rank. (or see attached file)

    Row\Column
    I
    J
    K
    L
    M
    4 Finished List
    5 Item# Description Rank Configuration
    6 40015 #6 x 3/4 1 HALF 1
    7 40060 #8 x 3/4 2 HALF 2

    Formula in I, J and L7
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula in K7
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula in M7
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I'm sorry, it can't be rearranged by order of rank. It must stay in order of description. Here's what I posted earlier that might help explain why.

    If you look at the the description of the items you'll see that they are actually sizes of product. This product list will later be represented as part of a display. The problem with shuffling them into the order of their rank is that the bin configuration will go out of wack. For example if you had six items in order of description that required FULL, HALF, FULL, HALF, HALF, HALF it would require five full bins because the first HALF would need to be by itself and the next two HALF's could share a bin while the last HALF would also be in a bin by itself. However, if the same products were reorganized by rank and it instead laid out as HALF, HALF, HALF, HALF FULL, FULL, for example, it would only need four drawers which wouldn't be the correct number of bins because the products have to be laid out in the order of their description size.

    The purpose of this program that I'm working on is to fit in as many of the top performing items that fit into a given display size. So if I have only 72 available spaces (36 bins) then I have to make sure I put in as many of the highest ranking items as possible given the available space. Please keep in mind that the finished product does not have to be accomplished in one step. It can be as many steps needed to accomplish the task.

  10. #10
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Create a dynamic list based on multiple dynamic factors.

    Sabin, I don't quite follow your explanations. Could you do two or three possible complete solutions in the worksheet so we can see what that will look like? It will make it easier to help you.
    Through in some comments on why the solutions look like they do and I'm sure we will solve this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create a dynamic list from multiple sheets
    By shbiskup in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-28-2022, 04:04 AM
  2. [SOLVED] Dynamic Unique List from multiple dynamic columns
    By JO505 in forum Excel General
    Replies: 7
    Last Post: 06-11-2015, 05:41 PM
  3. Want to Create a Dynamic List Excel based on a few columns in another sheet .
    By debsankardas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2013, 10:58 AM
  4. Urgent: Create a Dynamic Dropdown list based on a specific criteria
    By vijaye in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-11-2013, 08:07 AM
  5. [SOLVED] Create new list based on data from another dynamic list
    By y_not in forum Excel General
    Replies: 6
    Last Post: 01-04-2013, 09:16 AM
  6. [SOLVED] Create Dynamic Data Validation List based upon Criteria
    By stubbsj in forum Excel General
    Replies: 6
    Last Post: 06-04-2012, 08:33 AM
  7. create a dynamic drop down list based on specific criteria
    By stevegrobertson in forum Excel General
    Replies: 4
    Last Post: 02-21-2012, 08:07 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1