How to pull data from 4 different groups while staying under a set value

[alowishous]

I'm a self taught excel user and I'm struggling to find any information to help me with a formula that I'm not sure is even possible. I have a bunch of numbers already calculated throughout my spreadsheet and they have prices assigned to them. There are 4 different groups of numbers, I have to use a certain number of values from each group. I'm trying to figure out a formula that determines the highest number value while staying under a specific total price. As follows is an example of the types of numbers and situation i'm dealing with.

Group 1 Group 2 Group 3 Group 4
A B D E G H J K
1 Price Value Price Value Price Value Price Value
2 $50 22 $85 25 $69 26 $87 28
3 $58 19 $60 18 $45 16 $51 25
4 $66 21 $56 16 $62 24 $69 17
5 $82 25 $51 12 $65 23 $65 14
6 $102 30 $80 19 $78 24 $68 11
7 $81 23 $66 11 $81 24 $56 28
8 $90 19 $83 23
9 $51 10 $53 14
10 $66 13 $45 12
11 $84 15 $62 16
12 $63 11 $88 23
13 $60 9 $70 18

So that is basically what I'm working with. I need to use 1 from group 1, 2 from group 2, 2 from group 3, and 1 from group 4. The total price needs to stay under $600 as well. Once again I'm not sure if this is possible, but I appreciate any help anyone can give me. Thanks in advance.

[alowishous]

Well that didn't post how I thought it would. The groups and numbers are suppose to spread out and look like and excel spreadsheet.

[AlKey]

Hi alowishous,

I suggest you upload a spreadsheet instead pasting.

Click on go "Go Advanced" on the bottom of your screen the click on a paperclip icon and attached file.

[alowishous]

Example.xlsx

Hopefully this worked

[bmxfreedom]

The cases where you only need 1 value from group 1 and group 4 are easy because you can just use

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

to find the largest values in the Value columns, then

Please Login or Register  to view this content.

AND

Please Login or Register  to view this content.

to return the resulting values from the respective price columns. But because you have multiple instances of the values that are largest or 2nd largest in Groups 2 and 3, a formula will be very difficult. The following formula will give you the 2nd lowest value in column E: (NEVER MIND, that formula doesn't work either...) Finding the lowest Price value associated with this Value number will be even more tricky, or next to impossible, using formulas if the number of instances of this value are ever more than 7. (It could only be done with nested if formulas, of which, you can only have 7.)

[bmxfreedom]

Your eventual solution will likely be based in VBA. This would be a lot easier if the Price columns were sorted by value... I'm guessing that these amounts are entered manually. Code could be placed in the worksheet change event to sort the price/value columns by price automatically every time a price is entered. Then the formulas could rely on the fact that the prices exist in numerical order, so the 1st match of the largest value would return the smallest resulting price. (I assume that is the goal; attempting to use 2 values in Group 2 and 3, but keeping the price under $600 or as low as possible?)

[alowishous]

Yeah basically, these aren't the exact values i'm using, i'm just trying to figure out if it is possible. I want the formula to use x amount of values from each group to create the highest number while staying under a certain total price y. So this example needs to find the highest total number using 1 from group 1, 2 from 2, 2 from 3, and 1 from 4 while staying under the price 600. The prices used from each group doesn't matter, it just needs to find the highest number while staying under the set price. If that doesn't make sense I can try to explain it further. Thanks for your help bmxfreedom

[bmxfreedom]

Yeah, in this case, I think some code is in order... How do you intend to define the "x" values from each group and the "y" total value top-out? Are these going to be numbers a user would enter into a cell?

[bmxfreedom]

Well, it's some super shoddy code, but it kind of works... Maybe somebody with a better understanding that I can come up with a better solution.

Please Login or Register  to view this content.

This looks at the max $ as input value in cell M3, and a formula that calculates the sum of the total price in cell R5. I've attached the version of your workbook containing the formula and the code. Try it out. I hope you can make it work.

[bmxfreedom]

I left a few things undone in the code. You might want to update it to the following:

Please Login or Register  to view this content.