D Moran
New Member
Hi, I have a difficult problem to solve.
There are 7 groups and each group has a number of subjects. A person can pick 3 subjects but can only pick one subject from any one of the groups. so, they have 7 group options but they really end up picking one subject from 3 groups (as they are only allowed do 3 subjects).
Out of the 7 groups, there are 35 group combinations = 7x6x5/3x2x1 = 210/6 = 35 group combinations.
I want to have a program that will list out all the combinations of subjects for each of the 35 group combinations. So, the following code works perfectly for the group combinations - Group 1, Group 2 and Group 3. Group 1 has 3 subjects, Group 2 has 5 subjects and Group 3 has 6 subjects. Therefore, the possible combinations of subjects from Group 1, Group 2 and Group 3 are 90 combinations -> 3x5x6x= 90 combinations. The following code works for this. See attached spreadsheet example.
The problem is that I want to do the same thing for the remaining 34 group combinations and I don't want to write a sub function for each of these combinations and just wondering if there is a cleaner way to do this. Sorry I am new to VBA. The total combinations from all of the 35 combinations of groups is 1298. Any help appreciated.
There are 7 groups and each group has a number of subjects. A person can pick 3 subjects but can only pick one subject from any one of the groups. so, they have 7 group options but they really end up picking one subject from 3 groups (as they are only allowed do 3 subjects).
Out of the 7 groups, there are 35 group combinations = 7x6x5/3x2x1 = 210/6 = 35 group combinations.
I want to have a program that will list out all the combinations of subjects for each of the 35 group combinations. So, the following code works perfectly for the group combinations - Group 1, Group 2 and Group 3. Group 1 has 3 subjects, Group 2 has 5 subjects and Group 3 has 6 subjects. Therefore, the possible combinations of subjects from Group 1, Group 2 and Group 3 are 90 combinations -> 3x5x6x= 90 combinations. The following code works for this. See attached spreadsheet example.
The problem is that I want to do the same thing for the remaining 34 group combinations and I don't want to write a sub function for each of these combinations and just wondering if there is a cleaner way to do this. Sorry I am new to VBA. The total combinations from all of the 35 combinations of groups is 1298. Any help appreciated.
Code:
Sub List1stGroupCombinations()
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A4") 'First Group
Set xDRg2 = Range("B2:B6") 'Second Group
Set xDRg3 = Range("C2:C7") 'Third Group
xStr = "-" 'Separator
Set xRg = Range("I2") 'Output cell
For xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Text
For xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text
For xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
Set xRg = xRg.Offset(1, 0)
Next
Next
Next
End Sub
Attachments
Last edited by a moderator: