VBA Code - Combinations with Optimal Output

[blacklisted]

Hey Everyone,

I'm new to the forum and have very, very basic VBA skills. I have been searching around the internet trying to find combination VBA code that I could lift for my project I'm working on but I can not find anything that applies.

What I'm trying to do is find all the possible combinations of selecting 6 out of 30 employees for a project, which I know is just under 600K possible combinations. However, there is a monthly budget for the project of $50K which cannot be exceeded so that should bring the number of possible combinations down to what I hope is a more manageable list. Also, we rate each employee on a scale from 1 to 10, 10 being the best rating, so I would like to sort the output list by the sum of the 6 employees ranking so that I can focus on selecting the 6 best employees to work on the project for the optimal cost to maximize the value of the project.

My data is as follows:
A1:A30 -> Names of Employees
B1:B30 -> Employee Salaries
C1:C30 -> Rating between 1 & 10

I was hoping the output would be a list of all possible combination of the employee names in 6 separate columns, the 7th column would be the sum of the salaries, and the 8th column would be the sum of all 6 employee's rating (sorted by this column).

Please let me know if you have any questions and thanks in advance for your help.

 

[StephenCrump]

Welcome to the Forum!

The code below will output all possible combinations into columns 1 to 6.

Beyond that, it's not totally clear how you want to define the optimum combination, e.g. would you prefer a team with average rank 6, costing $30,000, or an A-team of 10's costing $45,000? Or, what about if the A-team cost only $35,000, i.e. what is the trade-off between cost and rank?

Sub Test()
    
    Dim lCombinations() As Integer
    
    lCombinations = GetCombinations(30, 6)
    [A1].Resize(UBound(lCombinations), UBound(lCombinations, 2)).Value = lCombinations

End Sub
Function GetCombinations(lNumber As Long, lNoChosen As Long) As Integer()

    Dim lOutput() As Integer, lCombinations As Long
    Dim i As Long, j As Long, k As Long
    
    lCombinations = WorksheetFunction.Combin(lNumber, lNoChosen)
    ReDim lOutput(1 To lCombinations, 1 To lNoChosen)
    
    For i = 1 To lNoChosen
        lOutput(1, i) = i
    Next i
    
    For i = 2 To lCombinations
        For j = 1 To lNoChosen
            lOutput(i, j) = lOutput(i - 1, j)
        Next j
        For j = lNoChosen To 1 Step -1
            lOutput(i, j) = lOutput(i, j) + 1
            If lOutput(i, j) <= lNumber - (lNoChosen - j) Then Exit For
        Next j
        For k = j + 1 To lNoChosen
            lOutput(i, k) = lOutput(i, k - 1) + 1
        Next k
    Next i
    
    GetCombinations = lOutput
    
End Function

 

[blacklisted]

Thank you for the code!

As far as the rankings, I think the first qualifier is that the team is equal to or under $50k and I was thinking the results would be sorted by total ranking (ie each employee is ranked 10 so they would be 60 total points). I think the average approach is a good upgrade rather than the sum because the scale from 1 to 10 means more than the sum. For the trade off between cost and ranking, I was planning to just do a sensitivity analysis manually of the data. For example, if the A team had an average ranking of 8 and cost $45k per month and B team had an average ranking of 7.5 but only cost $20k per month than I would choose the B team because they will provide a similar value at almost half the cost. I'm not sure how to define that in terms of coding so I was hoping this list would give me the ranking of all the combos and I would filter, sort, etc to determine the best value manually. The quality of the project is more important than the budget so the ranking is more important to answer your question but I also don't want to miss selecting a combo that has a decent ranking at a significantly lower cost. If I don't use all of the budget I just give it back so it's ok to use the full $50k but if another team makes more sense economically and I can get a similar quality then I want to consider that. After I've been through the data, I might have a better idea of how to quantatively define the optimal team.

Would you be able write the code to refine the results by the monthly salary constraint of $50k and then sort that list by the average ranking of the entire team?