VBA: Value permutations with parameters & from this combination with highest sum value in associated column

glowe2020

New Member
Joined
Dec 4, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi All & to anyone that can help!

I am somewhat of an excel novice and not too familiar with VBA's (Macro's) however I do know I need one and the logic behind what I am am trying to achieve..... so any help will be greatly appreciated.


I have a data table I wish to paste in each time I run the macro. It has x3 columns and is normally no more than 30 lines. (I have made up a table here for example)
NameProbabilityReturn $
James3.220
Bob2.512.5
Stacey8.95.8
Kevin77.6
Thomas77.6
Ellie1.105.6
Katryna1.210
Alison1.28

The first step would be: combinations of Names that result in a probability with a value less than 26, when multiplied. This is needed for combinations of 2 names, then 3 names and 4 names.
Ie
2: James & Bob
3.James, Bob, Katryna

& so on.

Now once the different permutations have been produced, from those permutations split into combinations of 2,3 & 4, of these combinations, which returns the high sum of returns when added together.

So when I paste the data in, when the macro is ran, I am wishing for the output to allow me to chose which name combination allows me to maximise my returns the most when combined the probability is less than the value of 26. (So probability is being multiplied & returns is the sum of)

Generating something like

2: James & Bob : $32.5
3.James, Bob, Katryna: $42.5
-------------------------------------
3.James, Bob, Alison: $40.5 X (so although this is viable, the sum of another combination is less so I don't want it returning)

Thank you to anyone that can help
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,541
Welcome to the MrExcel forum!

If you start with your sheet set up in columns A:C as shown:

Book3
ABCDEFG
1NameProbabilityReturn $CountNamesTotal Return
2James3.2202James,Bob32.5
3Bob2.512.53James,Bob,Katryna42.5
4Stacey8.95.84James,Bob,Katryna,Alison50.5
5Kevin77.6
6Thomas77.6
7Ellie1.15.6
8Katryna1.210
9Alison1.28
Sheet1


You can run a macro to get the results in columns E:G.

Open a copy of your workbook, make sure the data is in the right place. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste this code in the window that opens:

VBA Code:
Public NameList As String
Public MaxReturn As Double

Sub StartIt()
Dim MyData As Variant, MaxProb As Double, i As Long

    MyData = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    Range("E1:G1") = Array("Count", "Names", "Total Return")
    
    MaxProb = 26
    
    For i = 2 To 4
        NameList = ""
        MaxReturn = 0
        
        Call recur(0, i, "", 1, MaxProb, 0, 0, MyData)
        Cells(i, "E") = i
        Cells(i, "F") = Mid(NameList, 2)
        Cells(i, "G") = MaxReturn
    Next i
    
End Sub

Sub recur(CurLevel, MaxLevel, Names, Prob, MxProb, Retrn, Loc, ByRef MyData)
Dim i As Long

    If CurLevel = MaxLevel Then
        If Prob <= MxProb And Retrn > MaxReturn Then
            NameList = Names
            MaxReturn = Retrn
        End If
        Exit Sub
    End If
    
    For i = Loc + 1 To UBound(MyData)
        Call recur(CurLevel + 1, MaxLevel, Names & "," & MyData(i, 1), Prob * MyData(i, 2), MxProb, Retrn + MyData(i, 3), i, MyData)
    Next i
    
End Sub

Press Alt-Q to close the editor. Press Alt-F8 to open the macro selector. Choose StartIt and click Run.

Let us know how this works!
 

glowe2020

New Member
Joined
Dec 4, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Eric,

Not that I am religious but you to me are like God right now! Thank you ever so much, it works great!

Excel Genius!

Thanks

Guy
 

glowe2020

New Member
Joined
Dec 4, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel forum!

If you start with your sheet set up in columns A:C as shown:

Book3
ABCDEFG
1NameProbabilityReturn $CountNamesTotal Return
2James3.2202James,Bob32.5
3Bob2.512.53James,Bob,Katryna42.5
4Stacey8.95.84James,Bob,Katryna,Alison50.5
5Kevin77.6
6Thomas77.6
7Ellie1.15.6
8Katryna1.210
9Alison1.28
Sheet1


You can run a macro to get the results in columns E:G.

Open a copy of your workbook, make sure the data is in the right place. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste this code in the window that opens:

VBA Code:
Public NameList As String
Public MaxReturn As Double

Sub StartIt()
Dim MyData As Variant, MaxProb As Double, i As Long

    MyData = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    Range("E1:G1") = Array("Count", "Names", "Total Return")
   
    MaxProb = 26
   
    For i = 2 To 4
        NameList = ""
        MaxReturn = 0
       
        Call recur(0, i, "", 1, MaxProb, 0, 0, MyData)
        Cells(i, "E") = i
        Cells(i, "F") = Mid(NameList, 2)
        Cells(i, "G") = MaxReturn
    Next i
   
End Sub

Sub recur(CurLevel, MaxLevel, Names, Prob, MxProb, Retrn, Loc, ByRef MyData)
Dim i As Long

    If CurLevel = MaxLevel Then
        If Prob <= MxProb And Retrn > MaxReturn Then
            NameList = Names
            MaxReturn = Retrn
        End If
        Exit Sub
    End If
   
    For i = Loc + 1 To UBound(MyData)
        Call recur(CurLevel + 1, MaxLevel, Names & "," & MyData(i, 1), Prob * MyData(i, 2), MxProb, Retrn + MyData(i, 3), i, MyData)
    Next i
   
End Sub

Press Alt-Q to close the editor. Press Alt-F8 to open the macro selector. Choose StartIt and click Run.

Let us know how this works!

Hi Eric,

I would like to build on this and amend slightly, hoping again you can help.

I want to add to have combinations of 5, 6, 7, 8, 9 names as well.

Keeping the same existing parameters & logic I also wish to have the returns as a probability, so the returns of 20 would be 1.2 (20/100+1) and then work off these for the calculation, but this time instead of adding the returns, I wish to multiply them for the total return value.

The final part I wish to do was, with all the return totals for the different name combinations, add thee values:-

If there are 3 names, add 0.0475 to the return
If 4 names, add 0.0934
If 5 names, add 0.1101
If 6 names, add 0.1353
If 7 names, add 0.1774
If 8 names, add 0.218
If 9 names, add 0.2571

Regards

Guy
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,541
If I understand correctly:

VBA Code:
Public NameList As String
Public MaxReturn As Double

Sub StartIt()
Dim MyData As Variant, MaxProb As Double, i As Long, Bonus As Variant

    MyData = Range("A2:C" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    Range("E1:G1") = Array("Count", "Names", "Total Return")
    Bonus = Array(0, 0, 0, 0.0475, 0.0934, 0.1101, 0.1353, 0.1774, 0.218, 0.2571)
        
    MaxProb = 10000
    
    For i = 2 To 9
        NameList = ""
        MaxReturn = 0
        
        Call recur(0, i, "", 1, MaxProb, 1, 0, MyData)
        Cells(i, "E") = i
        Cells(i, "F") = Mid(NameList, 2)
        Cells(i, "G") = MaxReturn + Bonus(i)
    Next i
    
End Sub

Sub recur(CurLevel, MaxLevel, Names, Prob, MxProb, Retrn, Loc, ByRef MyData)
Dim i As Long

    If CurLevel = MaxLevel Then
        If Prob <= MxProb And Retrn > MaxReturn Then
            NameList = Names
            MaxReturn = Retrn
        End If
        Exit Sub
    End If
    
    For i = Loc + 1 To UBound(MyData)
        Call recur(CurLevel + 1, MaxLevel, Names & "," & MyData(i, 1), Prob * MyData(i, 2), MxProb, Retrn * (1 + MyData(i, 3) / 100), i, MyData)
    Next i
    
End Sub

Sheet set up the same way. I don't know what your probabilities are like, but on my test sheet, I had to raise the max probability much higher to get any valid results for a count of names above 4. And I also don't know how many names you have. If you have a large list, the number of combinations rises rapidly. It could take a long time to run. You may wish to change the

VBA Code:
For i = 2 to 9

line to

VBA Code:
For i = 2 to 5

and work your way up.
 

glowe2020

New Member
Joined
Dec 4, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi Eric, thanks for the latest solution.
I agree, when it goes over combo's of 5 it is probably too big to run.

However I when tried to run it, and it doesnt seem to be sticking to the constraint of max 26. For example on the count of 2 names, it is returning 2 names where the probability multiplies to 93.5.

The last macro you produced did stick to them.

Guy
 

Forum statistics

Threads
1,144,164
Messages
5,722,856
Members
422,461
Latest member
kelleys315

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top