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

[glowe2020]

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)

Name	Probability	Return $
James	3.2	20
Bob	2.5	12.5
Stacey	8.9	5.8
Kevin	7	7.6
Thomas	7	7.6
Ellie	1.10	5.6
Katryna	1.2	10
Alison	1.2	8

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

 

[Eric W]

Welcome to the MrExcel forum!

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

Book3
	A	B	C	D	E	F	G
1	Name	Probability	Return $		Count	Names	Total Return
2	James	3.2	20		2	James,Bob	32.5
3	Bob	2.5	12.5		3	James,Bob,Katryna	42.5
4	Stacey	8.9	5.8		4	James,Bob,Katryna,Alison	50.5
5	Kevin	7	7.6
6	Thomas	7	7.6
7	Ellie	1.1	5.6
8	Katryna	1.2	10
9	Alison	1.2	8
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:

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]

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]

Welcome to the MrExcel forum!

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

Book3
	A	B	C	D	E	F	G
1	Name	Probability	Return $		Count	Names	Total Return
2	James	3.2	20		2	James,Bob	32.5
3	Bob	2.5	12.5		3	James,Bob,Katryna	42.5
4	Stacey	8.9	5.8		4	James,Bob,Katryna,Alison	50.5
5	Kevin	7	7.6
6	Thomas	7	7.6
7	Ellie	1.1	5.6
8	Katryna	1.2	10
9	Alison	1.2	8
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:

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]

If I understand correctly:

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

For i = 2 to 9

line to

For i = 2 to 5

and work your way up.

 

[glowe2020]

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