+ Reply to Thread
Results 1 to 19 of 19

Choosing values from table

  1. #1
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Choosing values from table

    I am working with a table of numbers (and some empty cells), and I would like for Excel to choose the best set of numbers for my problem.

    -I can only have one chosen number per row and column.
    -The sum of the chosen numbers must be the minimum possible.
    -Excel needs to list which numbers should be chosen (either highlighted, listed, etc.)

    The tables I will be using will vary in the number of rows and columns, but for the sake of simplicity I have attached a sample table. For what it's worth, the real tables will be much larger than this sample. The cells highlighted are the ones that I want Excel to be able to pick out as the best solution (since only one cell is chosen per row and column, and the sum of these values is the lowest possible of all combinations).

    I hope I have been clear in what I am looking to do. If not, feel free to ask questions. I appreciate any help that can be provided!
    Attached Files Attached Files
    Last edited by turbosax2; 05-04-2011 at 08:47 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Choosing values from table

    Is this what you had in mind? Not sure of the original request but attempted to understand.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    No, that is not what I need. I need Excel to look at the entire table and figure out what set of numbers (with only one per row and column) sum to the lowest number. So in the example I posted, the highlighted numbers would be chosen because they sum to 4, which is the lowest possible, and there is only one number per row and only one number per column.

  4. #4
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    Any other ideas?

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Choosing values from table

    I cannot envision the logic necessary to complete this task. Do you have a logic plan you would like to share that you can explain simply.

    Alan

  6. #6
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    No, I do not have a logic plan. I don't really see any other way to do it besides calculating out every possible combination of numbers. I was hoping someone here could help with that part. Once I have a plan I believe I have enough competence with Excel to do the formulas.

  7. #7
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Choosing values from table

    This should do the trick if I understand you correctly.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    I can only have one chosen number per row and column, otherwise that would work.

    For example, if B2 is the lowest number in B1:B4, and C2 is the lowest number in C1:C4, that is not permissible because that is two chosen numbers per row.

  9. #9
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Choosing values from table

    Sorry for the delay.

    Try this..
    Please Login or Register  to view this content.
    Confirm with Ctrl Shift Enter

    See attachment and let me know if this works for you.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    Thank you for the help. I used your formulas and made a new table to test them. Something here doesn't work right. The values that should be selected to meet the criteria are highlighted.
    Attached Files Attached Files

  11. #11
    Forum Contributor day92's Avatar
    Join Date
    04-20-2010
    Location
    Los Angeles
    MS-Off Ver
    Excel 360
    Posts
    600

    Re: Choosing values from table

    I see what you need now. Not sure this can be done with a formula. You will need VBA to accomplish this. Maybe one of the VBA experts can write something for you.

  12. #12
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    Unfortunately, I am not proficient in VBA. If someone could help out I would greatly appreciate it!

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Choosing values from table

    Does this give you the information you need.

    Please Login or Register  to view this content.
    It will output the row/col and value in G1:I5

    It also uses A11:D14 as a working area.
    Cheers
    Andy
    www.andypope.info

  14. #14
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    From the first couple tests I've done, it looks like that will work! Many thanks!

  15. #15
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    I'm having trouble adapting this code to my spreadsheet. I get this error:
    "Run-time error '91': Object variable or With block variable not set"

    I believe it's because some columns and rows do not have values in them, but I'm not sure. I have attached the spreadsheet I'm working with. I'm running the macro on Sheet 5, and the working area is C3:IV51.

    Also, I'm not sure this code restricts one chosen number per row and column.
    Attached Files Attached Files
    Last edited by turbosax2; 05-25-2011 at 01:07 PM.

  16. #16
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    Perhaps I did not explain this problem quite right, either. One value must be chosen for each row. So in the spreadsheet I attached, there are four rows being looked at that actually include numbers (rows 3-6). One value must be chosen for each of them (the value that actually needs reported is the value from row 2, numbers 1-254). However, only one of these values can be chosen per column. So two rows can't both use values from column 4, for example.

    The number of rows will change every time I use this spreadsheet so that needs to be flexible.

  17. #17
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Choosing values from table

    Your example did not include data based on formula nor that the range could be empty.

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    05-04-2011
    Location
    PA
    MS-Off Ver
    Excel 2013
    Posts
    11

    Re: Choosing values from table

    Quote Originally Posted by Andy Pope View Post
    Your example did not include data based on formula nor that the range could be empty.
    I apologize for that. Obviously, keeping the example simple to avoid confusion backfired! Once again, thank you so much for writing that code

    If I wanted to alter that code to find the maximum values rather than the minimum values, is it as simple as changing "Min" to "Max"?
    Last edited by turbosax2; 05-27-2011 at 08:11 AM.

  19. #19
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Choosing values from table

    Yes using the MAX function should still work.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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