Maximum points generator chart

[selkov]

I manage a team that has a handicapped players skill level. There are 8 players on the team and we can play a maximum of 5 per week.
There is a target maximum combined skill level of 2375, if we exceed that we give up points.

Our players skill levels change sometimes weekly based on prior weeks performance so I always need to recalculate this.
I have tried to create a spreadsheet where i can type in the 8 players individual skill levels and have it show me all the possible combinations that will keep me un der the 2375 limit.
I can't do it.
Would someone be willing to take a crack at this for me?

Skill levels range form 100 to 900.

Thank you all.

 

[Eric W]

This might be possible with just formulas, but I found it much easier to write a macro.

Open a new workbook. Enter the names, skill levels and cap as follows:

Book2
	A	B	C	D
1	Player	Level		Max
2	A	100		2375
3	B	250
4	C	888
5	D	450
6	E	300
7	F	200
8	G	750
9	H	695
10
Sheet2

Now press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Then paste this code in the window that opens:

Public Teams As Variant

Sub GetTeams()
Dim Players As Variant, Cap As Long

    Set Teams = CreateObject("Scripting.Dictionary")
    
    Players = Range("A2:B9").Value
    Cap = Range("D2").Value
    
    Call Recur(Players, Cap, 0, 0, 0, "")
    
    Range("F:G").ClearContents
    Range("F1:G1").Value = Array("Total", "Roster")
    Range("F2").Resize(Teams.Count).Value = WorksheetFunction.Transpose(Teams.items)
    Range("G2").Resize(Teams.Count).Value = WorksheetFunction.Transpose(Teams.keys)
    
    
End Sub

Sub Recur(ByRef Players, ByRef Cap, ByVal level As Long, ByVal nextix As Long, ByVal tot As Long, ByVal roster As String)
Dim i As Long

    If tot > Cap Then Exit Sub
    
    If level = 5 Then
        Teams.Add Mid(roster, 2), tot
        Exit Sub
    End If
    
    For i = nextix + 1 To 8
        Call Recur(Players, Cap, level + 1, i, tot + Players(i, 2), roster & "," & Players(i, 1))
    Next i
        
    
End Sub

Press Alt-Q to close the VBA editor. Back in Excel press Alt-F8 to open the macro selector. Select GetTeams and click Run. You should end up with this:

Book2
	A	B	C	D	E	F	G
1	Player	Level		Max		Total	Roster
2	A	100		2375		1988	A,B,C,D,E
3	B	250				1888	A,B,C,D,F
4	C	888				1738	A,B,C,E,F
5	D	450				2288	A,B,C,E,G
6	E	300				2233	A,B,C,E,H
7	F	200				2188	A,B,C,F,G
8	G	750				2133	A,B,C,F,H
9	H	695				1300	A,B,D,E,F
10						1850	A,B,D,E,G
11						1795	A,B,D,E,H
12						1750	A,B,D,F,G
13						1695	A,B,D,F,H
14						2245	A,B,D,G,H
15						1600	A,B,E,F,G
16						1545	A,B,E,F,H
17						2095	A,B,E,G,H
18						1995	A,B,F,G,H
19						1938	A,C,D,E,F
20						2333	A,C,D,F,H
21						2238	A,C,E,F,G
22						2183	A,C,E,F,H
23						1800	A,D,E,F,G
24						1745	A,D,E,F,H
25						2295	A,D,E,G,H
26						2195	A,D,F,G,H
27						2045	A,E,F,G,H
28						2088	B,C,D,E,F
29						2333	B,C,E,F,H
30						1950	B,D,E,F,G
31						1895	B,D,E,F,H
32						2345	B,D,F,G,H
33						2195	B,E,F,G,H
34
Sheet2

Let us know how this works for you.

 

[kevin9999]

I think @Eric W has provided you with an excellent solution, but just to give you an option, here's a formula approach. Adjust the handicap skill levels in B2:B9, then you can use an Autofilter on Row 12, filtering column F to <2375. You will then get a list of all combinations that fall under that value.
(Filtered version shown)

COMBINATION PERMUTATION3.xls
	A	B	C	D	E	F
1	Players	H/Cap
2	1	358
3	2	608
4	3	504
5	4	852
6	5	448
7	6	408
8	7	472
9	8	233
10
11
12	HDR1	HDR2	HDR3	HDR4	HDR5	Total
17	1	2	3	5	6	2326
19	1	2	3	5	8	2151
20	1	2	3	6	7	2350
21	1	2	3	6	8	2111
22	1	2	3	7	8	2175
29	1	2	5	6	7	2294
30	1	2	5	6	8	2055
31	1	2	5	7	8	2119
32	1	2	6	7	8	2079
37	1	3	4	6	8	2355
39	1	3	5	6	7	2190
40	1	3	5	6	8	1951
41	1	3	5	7	8	2015
42	1	3	6	7	8	1975
44	1	4	5	6	8	2299
45	1	4	5	7	8	2363
46	1	4	6	7	8	2323
47	1	5	6	7	8	1919
55	2	3	5	6	8	2201
56	2	3	5	7	8	2265
57	2	3	6	7	8	2225
62	2	5	6	7	8	2169
67	3	5	6	7	8	2065
69
Sheet2

Cell Formulas
Range	Formula
F17,F19:F22,F29:F32,F37,F39:F42,F44:F47,F55:F57,F62,F67	F17	=SUM(SUMIFS($B$2:$B$9,$A$2:$A$9,A17:E17))

 

[kevin9999]

Here's the unfiltered version of the sheet:

player handicaps.xlsx
	A	B	C	D	E	F
1	Players	H/Cap
2	1	358
3	2	608
4	3	504
5	4	852
6	5	448
7	6	408
8	7	472
9	8	233
10
11
12	HDR1	HDR2	HDR3	HDR4	HDR5	Total
13	1	2	3	4	5	2770
14	1	2	3	4	6	2730
15	1	2	3	4	7	2794
16	1	2	3	4	8	2555
17	1	2	3	5	6	2326
18	1	2	3	5	7	2390
19	1	2	3	5	8	2151
20	1	2	3	6	7	2350
21	1	2	3	6	8	2111
22	1	2	3	7	8	2175
23	1	2	4	5	6	2674
24	1	2	4	5	7	2738
25	1	2	4	5	8	2499
26	1	2	4	6	7	2698
27	1	2	4	6	8	2459
28	1	2	4	7	8	2523
29	1	2	5	6	7	2294
30	1	2	5	6	8	2055
31	1	2	5	7	8	2119
32	1	2	6	7	8	2079
33	1	3	4	5	6	2570
34	1	3	4	5	7	2634
35	1	3	4	5	8	2395
36	1	3	4	6	7	2594
37	1	3	4	6	8	2355
38	1	3	4	7	8	2419
39	1	3	5	6	7	2190
40	1	3	5	6	8	1951
41	1	3	5	7	8	2015
42	1	3	6	7	8	1975
43	1	4	5	6	7	2538
44	1	4	5	6	8	2299
45	1	4	5	7	8	2363
46	1	4	6	7	8	2323
47	1	5	6	7	8	1919
48	2	3	4	5	6	2820
49	2	3	4	5	7	2884
50	2	3	4	5	8	2645
51	2	3	4	6	7	2844
52	2	3	4	6	8	2605
53	2	3	4	7	8	2669
54	2	3	5	6	7	2440
55	2	3	5	6	8	2201
56	2	3	5	7	8	2265
57	2	3	6	7	8	2225
58	2	4	5	6	7	2788
59	2	4	5	6	8	2549
60	2	4	5	7	8	2613
61	2	4	6	7	8	2573
62	2	5	6	7	8	2169
63	3	4	5	6	7	2684
64	3	4	5	6	8	2445
65	3	4	5	7	8	2509
66	3	4	6	7	8	2469
67	3	5	6	7	8	2065
68	4	5	6	7	8	2413
69
Sheet2

Cell Formulas
Range	Formula
F13:F68	F13	=SUM(SUMIFS($B$2:$B$9,$A$2:$A$9,A13:E13))

 

[selkov]

Eric - Great approach, did not think of a macro. Works great. tks

Kevin, thanks for your solution also, sadly i could not get it to run not sure what cellos to place the formula into nor what all the hrd1 thru hrd5 headers are for.

 

[selkov]

Eric,
Just found one issue with this macro. My fault when I said there were 8 players on the roster, that is MAX players allowed.
Some teams have only 6 or seven.
When I reduce the # of players I get inaccurate results, that is to say it displays counts of 4 ppl or less. It always has to be 5.
Can you adjust for that?

player	level				Total	Roster
1​	100​				1969​	1,2,3,4,5
2​	250​				2004​	1,2,3,4,6
3​	850​				2067​	1,2,3,4,7
4​	357​				1557​	1,2,3,4,
5​	412​				2059​	1,2,3,5,6
6​	447​				2122​	1,2,3,5,7
7​	510​				1612​	1,2,3,5,
					2157​	1,2,3,6,7
					1647​	1,2,3,6,
					1710​	1,2,3,7,

 

[Eric W]

Sure, try this:

Public Teams As Variant

Sub GetTeams()
Dim Players As Variant, Cap As Long

    Set Teams = CreateObject("Scripting.Dictionary")
    
    Players = Range("A2:B" & Range("A2").End(xlDown).Row).Value
    Cap = Range("D2").Value
    
    Call Recur(Players, Cap, 0, 0, 0, "")
    
    Range("F:G").ClearContents
    Range("F1:G1").Value = Array("Total", "Roster")
    Range("F2").Resize(Teams.Count).Value = WorksheetFunction.Transpose(Teams.items)
    Range("G2").Resize(Teams.Count).Value = WorksheetFunction.Transpose(Teams.keys)
    
End Sub

Sub Recur(ByRef Players, ByRef Cap, ByVal level As Long, ByVal nextix As Long, ByVal tot As Long, ByVal roster As String)
Dim i As Long

    If tot > Cap Then Exit Sub
    
    If level = 5 Then
        Teams.Add Mid(roster, 2), tot
        Exit Sub
    End If
    
    For i = nextix + 1 To UBound(Players)
        Call Recur(Players, Cap, level + 1, i, tot + Players(i, 2), roster & "," & Players(i, 1))
    Next i
        
End Sub

I only had to change 2 lines. It now looks for the bottom row. Make sure there are no gaps in the list, and that the rows following the names are empty.

 

[selkov]

Eric,
Thank you for what you've done so far.
I had an illness in the family and had to quit for a bit.
Getting back yo this now and am still getting an error.

here is the sample data I'm using. The macro errors out on line "Range("F2").Resize(Teams.Count).Value = WorksheetFunction.Transpose(Teams.Items)"

Player	skill
edward	514​
larry	458​
britney	342​
doug	424​
skip	539​
brendan	503​
jerry	459​
john	469​

 

[Eric W]

I'm sorry to hear about your family's illness. I hope things are better.

As far as the macro, did you put the cap in cell D2? If not, that would cause the error you're seeing.

 

[selkov]

Opps thought i did.... tks