Complicated issue in Excel

[richard1234567891011]

Hello guys. I have an issue. I have to sort the data, but optimised in order to be less than 100.000 but the sum nearest possible.
Virtually I need a way in order to have some data, I want that data selected by the excel (preference for the parameter of the data, but the main preference has to be in order to reach 100.000). I think it is not clear so I will make 2 examples.

Example 1

Parameter Data
1 _______10.000
0,9_____ 8.000
0,8 ______10.000
0,7______ 12.000

Parameter are the best solutions in order, but the main goal is to reach a value nearest possible to 100.000. So the solution here is all. Because the sum is less than 100.000

Parameter __Data
1_______ 15.000
0,9 ______10.000
0,8 ______10.000
0,7 ______10.000
0,6 ______10.000
0,5 _______5.000
0,4 ______10.000
0,3 ______10.000
0,2_____ 10.000
0,1_____ 10.000
0 ____________0

This point is tricky because this is in fact the main point. In this point I want excluded 5.000 because even if it has a better parameter the sum is not optimised in order to reach 10.000. The best way in order to reach 100.000 is to sum everything and excluding 5.000.
I hope is it clear.
How can I set something like that? I thought of the solver but how can I set a solver in this way?

Thank you. I really hope that it is clear.

 

[Exceladd1ct]

Hello, what is the relationship between Data and Parameters? What gives a certain Data value a certain Parameter? I see in your second example that identic Data values has different parameters.

 

[Exceladd1ct]

Supposing the values from your second example are in column A:

Dim x, val, Sum
Range("A1:A11").Sort Key1:=Range("A1"), Order1:=xlDescending


For x = 1 To 11 Step 1
val = Cells(x, 1).Value
'MsgBox Cells(x, 1)
If val <> "0" Then


If Sum < 100000 Then
Sum = Sum + val
If Sum >= 100000 Then
MsgBox "Limit reached: " & Sum & " We are Substracting last Value. (A" & x & " value: " & val & ")"
Sum = Sum - val
MsgBox "Current value reached:" & Sum
Else
MsgBox "Current value reached:" & Sum
End If


Else
MsgBox "Limit reached: " & Sum
End If


Else


MsgBox Sum & " :is the optimum value."


End If


Next

 

[richard1234567891011]

Thank you very much Exceladd1ct. I am not very skilled with macros. How can I used it? Can I used it with a Botton or I have to use something different?
The parameters are based on formulas. It is pointless if are the same. I used the same in order to simplify.

 

[Exceladd1ct]

That macro is for testing purposes, it might need to be improved. The code will do the following:

1.It will sort all values descending.
2. It will start summing the values until 100.000 limit is reached or exceeded.
3. Once the limit is reached or exceeded, it will subtract the last added value in order to go back under 100.000 and it will go to the next value in the list, repeating step 2 until the end of values list.

You can insert your values from the second example in colum A starting with A1, then go to Developer tab, Click Insert, draw a button on your sheet, click new in the pop-up window and insert my code inside that Sub.

Please let me know if it works and if it does what you need.