Listing all possible combinations with a few constraints

T

tigre8989

New Member
Joined
Nov 10, 2015
Messages
3
Hi all-

My question relates to fantasy football. In excel i have 5 columns (QB, RB, WR, TE, DEF). Each column will have 3-5 individual players. I want to create a list with every possible combination...Except there are a few wrinkles.. #1 there needs to be 2 RB, #2 there needs to be 3 WR, #3 there also needs to be a "FLEX" position which is one player from either the WR, RB or TE column, and #4 (and maybe the hardest) there is a price constraint. Ie each player will be assigned a price and the total "line up" must not exceed "x" price.

Therefore a final line up output should look like QB, RB, RB, WR, WR, WR, FLEX, TE, DEF where the total $ value should not equal more than "x"

Again, would really appreciate any guidance or even a start! Let me know if i should provide any further clarity.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
If you do not get a suitable solution, try the process below (only the first part has been checked):

1. Go to this online permutation generator:


Permutation Generator

enter your objects (QB, RB, RB, WR, WR, WR, TE, FLEX, DEF), choose space as delimiter, and click on „Generate permutations”.

2. Click on Save as, so the list will be saved as Output.txt.
3. Open this file in Notebook and copy into column A of an excel spreadsheet.
4. Select column A, go to Data and click on Remove duplicates. You get 30240 unique values.
5. Select the first, second or third 10080 rows of the list and replace FLEX with WR, RB or TE, respectively.
6. Select the whole list and click on Text to Columns, then copy the 9 columns you get next to the original 9 columns.
7. Select all the copied columns, and replace QB, RB, WR, TE and DEF with their assigned prize, resp.
8. Next to the prize columns SUM the columns to get the sum of each row (copy down the SUM formula).
9. Select the whole table from A to the sum column, and SORT it according to the SUM column, so you can easily find where the suitable x values are located.
 
Upvote 0
Hi Istvan, thanks for getting back to me. Just to be clear, each "position" will have multiple players associated to it. So for example for QB i can have Manning, Brady and Rodgers. RB will have ~4 players, WR ~4, etc. Therefore i will have a complete list of players that I want to see all possible combination of line ups for (which need to be 1 QB, 2 RB, 3 WR, 1 TE, 1 FLEX and 1 DEF).

I am not sure the tool you provided can provide the specific numbers associated with each position? (if that makes sense).

Thanks again for your quick response.
 
Last edited:
Upvote 0
You are welcome.

I am afraid the ’practical approach’ described above can not be used if further sub-elements are added to the elements to permutate as computing and memory problems would soon arise.
 
Upvote 0
You sort of piqued my interest, so I wrote something up, but I don't think it will do you much good. The number of combinations rises so rapidly that you're buried in a sea of options. You can take the list of results and sort by payroll, so you get the most bang for your buck. Or you can try to include the rating to get the highest combined rating, but that's a lot of extra work. And since the salary is an indication of rating anyway, you won't gain much. In any case, here's what I have:

Set up your first sheet in the workbook like so:
Excel 2010
ABCDEFGHIJKLMNOPQ
QB1RB1WR1TE1DEF1
QB2RB2WR2TE2DEF2
QB3RB3WR3TE3DEF3
RB4WR4TE4
WR5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #00B050"]QB[/TD]
[TD="bgcolor: #00B050, align: right"]1[/TD]
[TD="bgcolor: #00B050, align: right"][/TD]
[TD="bgcolor: #00B050"]RB[/TD]
[TD="bgcolor: #00B050, align: right"]2[/TD]
[TD="bgcolor: #00B050, align: right"][/TD]
[TD="bgcolor: #00B050"]WR[/TD]
[TD="bgcolor: #00B050, align: right"]3[/TD]
[TD="bgcolor: #00B050, align: right"][/TD]
[TD="bgcolor: #00B050"]TE[/TD]
[TD="bgcolor: #00B050, align: right"]1[/TD]
[TD="bgcolor: #00B050, align: right"][/TD]
[TD="bgcolor: #00B050"]DEF[/TD]
[TD="bgcolor: #00B050, align: right"]1[/TD]
[TD="bgcolor: #00B050, align: right"][/TD]
[TD="bgcolor: #00B050, align: right"][/TD]
[TD="bgcolor: #00B050"]Budget[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]110[/TD]
[TD="align: right"][/TD]

[TD="align: right"]60[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]650[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]150[/TD]
[TD="align: right"][/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]

[TD="align: right"]75[/TD]
[TD="align: right"][/TD]

[TD="align: right"]70[/TD]
[TD="align: right"][/TD]

[TD="align: right"]130[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]125[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"]80[/TD]
[TD="align: right"][/TD]

[TD="align: right"]93[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]99[/TD]
[TD="align: right"][/TD]

[TD="align: right"]85[/TD]
[TD="align: right"][/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]47[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3​


Row 1 contains the headers. Under each column put your player's name, with the salary to the right. To the right of each position header is the number of players of this type to be included in the final list. This is really just informational, since that number is hardcoded in the program. Also since the RB, WR, and TE categories can have a flex player.

Once you have this built follow these steps to install the program:
1) Press Alt-F11 to open the VBA editor
2) From the menu, click Insert --> module
3) In the window that opens up, paste this code:
Rich (BB code): 
Sub Fantasy()
Dim Options(5, 9, 2), Draft As Worksheet, Teams As Worksheet
Dim RBList(100), WRList(100), TEList(100)
Dim RBMin As Byte, WRMin As Byte, TEMin As Byte
Dim Budget As Double


    Set Draft = Sheets("Sheet3")
    Set Teams = Sheets("Sheet4")
    Teams.Cells.ClearContents
    Teams.Cells(1, 1) = "QB"
    Teams.Cells(1, 2) = "RB"
    Teams.Cells(1, 3) = "RB"
    Teams.Cells(1, 4) = "WR"
    Teams.Cells(1, 5) = "WR"
    Teams.Cells(1, 6) = "WR"
    Teams.Cells(1, 7) = "TE"
    Teams.Cells(1, 8) = "DEF"
    Teams.Cells(1, 9) = "FLEX"
    Teams.Cells(1, 11) = "Payroll"
    
    
    Options(1, 0, 1) = Draft.Columns(1).Find("").Row - 2
    Options(2, 0, 1) = Draft.Columns(4).Find("").Row - 2
    Options(3, 0, 1) = Draft.Columns(7).Find("").Row - 2
    Options(4, 0, 1) = Draft.Columns(10).Find("").Row - 2
    Options(5, 0, 1) = Draft.Columns(13).Find("").Row - 2
    For r = 2 To 10
        Options(1, r - 1, 1) = Draft.Cells(r, 1)
        Options(1, r - 1, 2) = Draft.Cells(r, 2)
        Options(2, r - 1, 1) = Draft.Cells(r, 4)
        Options(2, r - 1, 2) = Draft.Cells(r, 5)
        Options(3, r - 1, 1) = Draft.Cells(r, 7)
        Options(3, r - 1, 2) = Draft.Cells(r, 8)
        Options(4, r - 1, 1) = Draft.Cells(r, 10)
        Options(4, r - 1, 2) = Draft.Cells(r, 11)
        Options(5, r - 1, 1) = Draft.Cells(r, 13)
        Options(5, r - 1, 2) = Draft.Cells(r, 14)
    Next r
    
    RBMin = Draft.Cells(1, "E")
    WRMin = Draft.Cells(1, "H")
    TEMin = Draft.Cells(1, "K")
    Budget = Draft.Cells(2, "Q")
    
' Case 1 - The Flex player is RB
    Erase RBList, WRList, TEList
    Call Combos(RBMin + 1, Left("123456789", Options(2, 0, 1)), "", RBList)
    Call Combos(WRMin, Left("123456789", Options(3, 0, 1)), "", WRList)
    Call Combos(TEMin, Left("123456789", Options(4, 0, 1)), "", TEList)
    
    Call CheckAndDisplay(Options, Budget, RBList, WRList, TEList, 1, Teams)
    
' Case 2 - The Flex player is WR
    Erase RBList, WRList, TEList
    Call Combos(RBMin, Left("123456789", Options(2, 0, 1)), "", RBList)
    Call Combos(WRMin + 1, Left("123456789", Options(3, 0, 1)), "", WRList)
    Call Combos(TEMin, Left("123456789", Options(3, 0, 1)), "", TEList)
    
    Call CheckAndDisplay(Options, Budget, RBList, WRList, TEList, 2, Teams)
    
' Case 3 - The Flex player is TE
    Erase RBList, WRList, TEList
    Call Combos(RBMin, Left("123456789", Options(2, 0, 1)), "", RBList)
    Call Combos(WRMin, Left("123456789", Options(3, 0, 1)), "", WRList)
    Call Combos(TEMin + 1, Left("123456789", Options(4, 0, 1)), "", TEList)
    
    Call CheckAndDisplay(Options, Budget, RBList, WRList, TEList, 3, Teams)


End Sub
Public Sub CheckAndDisplay(Options, Budget, RBList, WRList, TEList, MyCase, Teams)
Dim ResLine(1, 11), Payroll As Double
Dim QB As Byte, RB As Byte, WR As Byte, TE As Byte, DF As Byte


    For QB = 1 To Options(1, 0, 1)
        For DF = 1 To Options(5, 0, 1)
            For RB = 1 To RBList(0)
                For WR = 1 To WRList(0)
                    For TE = 1 To TEList(0)
                        
                        'QB
                        ResLine(1, 1) = Options(1, QB, 1)
                        Payroll = Options(1, QB, 2)
                        
                        ' RB
                        ResLine(1, 2) = Options(2, Mid(RBList(RB), 1, 1), 1)
                        Payroll = Payroll + Options(2, Mid(RBList(RB), 1, 1), 2)
                        ResLine(1, 3) = Options(2, Mid(RBList(RB), 2, 1), 1)
                        Payroll = Payroll + Options(2, Mid(RBList(RB), 2, 1), 2)
                        If MyCase = 1 Then
                            ResLine(1, 9) = Options(2, Mid(RBList(RB), 3, 1), 1)
                            Payroll = Payroll + Options(2, Mid(RBList(RB), 3, 1), 2)
                        End If
                        
                        ' WR
                        ResLine(1, 4) = Options(3, Mid(WRList(WR), 1, 1), 1)
                        Payroll = Payroll + Options(3, Mid(WRList(WR), 1, 1), 2)
                        ResLine(1, 5) = Options(3, Mid(WRList(WR), 2, 1), 1)
                        Payroll = Payroll + Options(3, Mid(WRList(WR), 2, 1), 2)
                        ResLine(1, 6) = Options(3, Mid(WRList(WR), 3, 1), 1)
                        Payroll = Payroll + Options(3, Mid(WRList(WR), 3, 1), 2)
                        If MyCase = 2 Then
                            ResLine(1, 9) = Options(3, Mid(WRList(WR), 4, 1), 1)
                            Payroll = Payroll + Options(3, Mid(WRList(WR), 4, 1), 2)
                        End If
                        
                        ' TE
                        ResLine(1, 7) = Options(4, Mid(TEList(TE), 1, 1), 1)
                        Payroll = Payroll + Options(4, Mid(TEList(TE), 1, 1), 2)
                        If MyCase = 3 Then
                            ResLine(1, 9) = Options(4, Mid(TEList(TE), 2, 1), 1)
                            Payroll = Payroll + Options(4, Mid(TEList(TE), 2, 1), 2)
                        End If
                        
                        ' DEF
                        ResLine(1, 8) = Options(5, DF, 1)
                        Payroll = Payroll + Options(5, DF, 2)
                        
                        If Payroll < Budget Then
                            ResLine(1, 11) = Payroll
                            r = Teams.Columns(1).Find("").Row
                            For c = 1 To 11
                                Teams.Cells(r, c) = ResLine(1, c)
                            Next c
                        End If
                        
                    Next TE
                Next WR
            Next RB
        Next DF
    Next QB
            
End Sub




Public Sub Combos(TotCount, ListIn, ListOut, ResultAr)


    If Len(ListOut) = TotCount Then
        a = ResultAr(0)
        a = a + 1
        ResultAr(a) = ListOut
        ResultAr(0) = a
        Exit Sub
    End If
    
    For i = 1 To Len(ListIn) + Len(ListOut) + 1 - TotCount
        Call Combos(TotCount, Mid(ListIn, i + 1), ListOut & Mid(ListIn, i, 1), ResultAr)
    Next i


End Sub
4) Change the names of the input and output sheets (in red) to match your workbook.
5) Return to Excel
6) Press Alt-F8, select Fantasy, click Run.
7) Look at the results on the output page, they should look like:
Excel 2010
ABCDEFGHIJK
QB1RB1RB2WR2WR3WR5TE1DEF1RB3
QB1RB1RB2WR2WR3WR5TE2DEF1RB3
QB1RB1RB2WR2WR3WR5TE4DEF1RB3
QB1RB1RB2WR3WR4WR5TE1DEF1RB3
QB1RB1RB2WR3WR4WR5TE4DEF1RB3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #00B050"]QB[/TD]
[TD="bgcolor: #00B050"]RB[/TD]
[TD="bgcolor: #00B050"]RB[/TD]
[TD="bgcolor: #00B050"]WR[/TD]
[TD="bgcolor: #00B050"]WR[/TD]
[TD="bgcolor: #00B050"]WR[/TD]
[TD="bgcolor: #00B050"]TE[/TD]
[TD="bgcolor: #00B050"]DEF[/TD]
[TD="bgcolor: #00B050"]FLEX[/TD]
[TD="bgcolor: #00B050, align: right"][/TD]
[TD="bgcolor: #00B050"]Payroll[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]632[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]642[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]622[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]642[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]632[/TD]

</tbody>
Sheet4​


Odds are you'll get hundreds, if not thousands of options. Try sorting by payroll.

Good luck. Any changes to this are up to you.
 
Upvote 0
Sorry, found a typo: Look for this section of code:
Rich (BB code): 
' Case 2 - The Flex player is WR
    Erase RBList, WRList, TEList
    Call Combos(RBMin, Left("123456789", Options(2, 0, 1)), "", RBList)
    Call Combos(WRMin + 1, Left("123456789", Options(3, 0, 1)), "", WRList)
    Call Combos(TEMin, Left("123456789", Options(3, 0, 1)), "", TEList)
and change the 3 in red to a 4.
 
Upvote 0
Hi Eric,

You seem to be an expert in Excel VBA.
Sorry for jumping an old thread but I''ve seen your code and it's what I was lookin for.
Excuse me in advance for my newbie wording. That's the first time I will try to use VBA.
I'm trying to do something kind of same than what Tigre8989 wanted to do. I was struggling to find excel formula then I saw your solution.
Basically I would like to obtain unique combinations for like 1QB, 2RB, 2WR only (+ Budget as you've done).

I have tried to amend the code but it's blocking me at the "for r=" step and I can't find why :s

On top of that, as I'm a beginner (sorry again), I don't understand properly if your code is already excluding duplicates such as:
1)QB1/RB1/RB3/WR1/WR2 and QB1/RB3/RB1/WR1/WR2

2)QB1/RB1/RB1/WR1/WR2

Could you please give me some help on this or direct me to something which could help me to become skilled in vba writing and understanding?
Thanks a lot in advance.
Rikko
 
Upvote 0
Hi Rikko, welcome to the forum.

Wow, I wrote that over 3 years ago. In looking over it, I see a lot of things I'd do differently now. But it should still work OK. Yes, it should exclude duplicates like your examples. I can't imagine why the "For r=" line is failing, that's pretty basic. Is it failing on that exact line, or in the loop somewhere? What error message do you get? Is your spreadsheet set up like the example?

As far as helping you learn VBA, I'm the wrong person to ask. I've been a programmer for over 30 years, with probably 15 or so in VBA. I'm largely self-taught by reading manuals, help screens, web links, etc. You might want to look up a book such as VBA for Dummies. One of our members, @hiker95 , has compiled a huge list of web resources, which you can find here:

https://www.mrexcel.com/forum/gener...referral-class-best-suited-data-clean-up.html

It's a vast list and can be a bit intimidating, but just pick one item, work at it until it makes sense, then try something else. There are YouTube videos if that's your thing. There's a lot of duplication in the list, and some topics just won't interest you. But if you are serious about learning VBA, there is definitely something to help.

Good luck!
 
Upvote 0
Hi Eric,

Thanks a lot for your reply and advice.
Thanks to this now I understand what "for r =" means which is more than a good beginning lol :D
I will try to amend the code in order to make it work based on my requirements.
Would you be ok if I share my progress with you here?

Thanks a lot in advance
Rikko
 
Upvote 0
Eric W said:
I can't imagine why the "For r=" line is failing, that's pretty basic. Is it failing on that exact line, or in the loop somewhere? What error message do you get? Is your spreadsheet set up like the example?

Good luck!
Click to expand...

Hi Eric,
Basically the script is projecting the headers of the columns only. I don't know why :s
Sub Fantasy()
Dim Options(5, 9, 2), Draft As Worksheet, Teams As Worksheet
Dim RBList(100), WRList(100), TEList(100)
Dim RBMin As Byte, WRMin As Byte, TEMin As Byte
Dim Budget As Double


Set Draft = Sheets("Sheet3")
Set Teams = Sheets("Sheet4")
Teams.Cells.ClearContents
Teams.Cells(1, 1) = "QB"
Teams.Cells(1, 2) = "RB"
Teams.Cells(1, 3) = "RB"
Teams.Cells(1, 4) = "WR"
Teams.Cells(1, 5) = "WR"
Teams.Cells(1, 6) = "WR"
Teams.Cells(1, 7) = "TE"
Teams.Cells(1, 8) = "DEF"
Teams.Cells(1, 9) = "FLEX"
Teams.Cells(1, 11) = "Payroll"


So this part works correctly then nothing happens. The loop turns without showing anything.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Emerlin
  • Solved
Formula needed to filter and sort data from range - Excel 2016.
2
Replies
10
Views
394
Peter_SSs
Peter_SSs
M
  • Solved
Find 2nd Value in a Row, Return Adjacent Cell
2
Replies
12
Views
843
Fluff
Fluff
C
  • Question Question
Help with rank using countifs?
Replies
2
Views
565
cspengel
C
W
  • Question Question
Optimizing a Fantasy Team in Excel
Replies
7
Views
4K
Eric W
Eric W
D
  • Question Question
Generate Unique combinations in VBA of values with a sum constraint
Replies
0
Views
1K
DonKampfello
D

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

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
Back
Top