I'm working on a scorecard at work which has columns representing 4 possible outcomes, for example:
Each staff member is assessed 5 times in the month against those ratings. So 1 person might have 3 successful, 2 exceptional, 0 unsuccessful, 0 other.
So the max instances of each outcome is 5 but the total sum of instances can't be more than 5.
I could try to type out all the combinations (and get them wrong) - is there any function/formula/VBA that anyone knows of that will list out all of the possible combinations of outcomes for me?
e.g. 5000,4100,4010,4001,3200,3020,3002,3110,3011, etc...
Since your numbers can range from 0005
to 5000
, you could just write a simple loop that tests each number to see if the digits total 5
:
Sub GetPermutations()
Dim i As Long
For i = 5 To 5000
If SumDigits(i) = 5 Then Debug.Print Format$(i, "0000")
Next
End Sub
Function SumDigits(s As Variant) As Long
Dim i As Long
For i = 1 To Len(s)
SumDigits = SumDigits + CLng(Mid$(s, i, 1))
Next
End Function
Alternatively:
Dim w As Long, x As Long, y As Long, z As Long
For w = 0 To 5
For x = 0 To 5
For y = 0 To 5
For z = 0 To 5
If w + x + y + z = 5 Then Debug.Print w & x & y & z
Next
Next
Next
Next
c#:
// numbering 1 to 4 - i.e.
// 1 sucessful, 2 unsucessful, 3 exception, 4 other
for(int i=1;i<4;i++)
{
for(int n=1;n<4;n++)
{
for(int d=1;d<4;d++)
{
for(int q=1;q<4;q++)
{
if(i+d+n+q<=5)
Console.WriteLine(i+n+d+q+", ");
}
}
}
}
EDIT: just saw you asked for vba:
For number1 As Integer = 1 To 4 Step 1
For number2 As Integer = 1 To 4 Step 1
For number3 As Integer = 1 To 4 Step 1
For number4 As Integer = 1 To 4 Step 1
if(number1+number2+number3+number4<=5) Then
Debug.WriteLine(number1.ToString & number2.toString &number3.toString &number4.ToString & ",");
End If
Next number4
Next number3
Next number2
Next number1
The most simple solution I could think of, there's probably better though.
Thanks for your help everyone - i managed to use a combination of suggestions by starting with the number 1, auto-filling down to 5000, then "text to columns", fixed width to separate the digits, sum to 5, filter and hey presto! Seems to have produced the right number of possible combinations adding up to 5.
Thanks again for your help!