How can I find all possible combinations of words?

[Yvonne]

OK, I am preparing a survey, and I have to be able to calculate all
combinations of the multiple choice answers.

Example Question: Where do you save your emails? (Check all that apply)

Example Answers:

S: Drive
P: Drive
SharePoint/DocManage
Other

I have to take all of the above multiple choice answers to the example
question and project all possible combinations of answers. Is there a way to
do this with excel? If not, I have to figure out all possible combinations
manually.

I appreciate any input, as it will save me time.

Thanks

 

[Jim Cone]

Technically you want permutations for the 4 items (their order doesn't matter)
s = sdrive, p = pdrive, d= docmanage, o = other
* 24 *
dops
dosp
dpos
dpso
dsop
dspo
odps
odsp
opds
opsd
osdp
ospd
pdos
pdso
pods
posd
psdo
psod
sdop
sdpo
sodp
sopd
spdo
spod
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins -- free trial "Display Word Permutations"-- no registration)




"Yvonne"
wrote in message
OK, I am preparing a survey, and I have to be able to calculate all
combinations of the multiple choice answers.
Example Question: Where do you save your emails? (Check all that apply)
Example Answers:

S: Drive
P: Drive
SharePoint/DocManage
Other

I have to take all of the above multiple choice answers to the example
question and project all possible combinations of answers. Is there a way to
do this with excel? If not, I have to figure out all possible combinations
manually.
I appreciate any input, as it will save me time.
Thanks

 

[Jim Cone]

Fingers were working faster than my brain.
This list makes more sense...

1
2
3
4
1 2
1 3
1 4
2 3
2 4
3 4
1 2 3
1 2 4
1 3 4
2 3 4
1 2 3 4

 

[ryguy7272]

Sub Combinations()
Dim i As Integer
Dim A(1 To 5) As Integer
Dim B As Variant

For i = 1 To 5
A(i) = i
Next i
B = Array("S: Drive", "P: Drive", "SharePoint/DocManage", "Other")

'MsgBox ListSubsets(B)
Range("A1") = ListSubsets(B)


End Sub

 

[ytayta555]

HI

I think what you need is combinations ; we suppose you
have 7 answer to choice ; in this case , try this macro :

Sub Combins7()
'Calculates the sum of the values of all combinations of 7 things
taken 1-7 at a time.
'The 7 individual piece values are assumed to be in cells A1:A7
'Combins7 will place the various combinations in C1:C127

Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim i5 As Integer
Dim i6 As Integer
Dim i7 As Integer
Dim iRow As Integer

iRow = 0

'Combin(7,1) (seven things taken 1 at a time)
For i1 = 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A")
Next i1

'Combin(7,2) (seven things taken 2 at a time)
For i1 = 1 To 6
For i2 = i1 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A")
Next i2
Next i1

'Combin(7,3)
For i1 = 1 To 5
For i2 = i1 + 1 To 6
For i3 = i2 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A")
Next i3
Next i2
Next i1

'Combin(7,4)
For i1 = 1 To 4
For i2 = i1 + 1 To 5
For i3 = i2 + 1 To 6
For i4 = i3 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A") + Cells(i4, "A")
Next i4
Next i3
Next i2
Next i1

'Combin(7,5)
For i1 = 1 To 4
For i2 = i1 + 1 To 4
For i3 = i2 + 1 To 5
For i4 = i3 + 1 To 6
For i5 = i4 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A") _
+ Cells(i3, "A") + Cells(i4, "A") _
+ Cells(i5, "A")
Next i5
Next i4
Next i3
Next i2
Next i1

'Combin(7,6)
For i1 = 1 To 2
For i2 = i1 + 1 To 3
For i3 = i2 + 1 To 4
For i4 = i3 + 1 To 5
For i5 = i4 + 1 To 6
For i6 = i5 + 1 To 7
iRow = iRow + 1
Cells(iRow, "C") = Cells(i1, "A") + Cells(i2, "A")
_
+ Cells(i3, "A") + Cells(i4, "A")
_
+ Cells(i5, "A") + Cells(i6, "A")
Next i6
Next i5
Next i4
Next i3
Next i2
Next i1

'Combin(7,7)
iRow = iRow + 1
Cells(iRow, "C") = 0
For i1 = 1 To 7
Cells(iRow, "C") = Cells(iRow, "C") + Cells(i1, "A")
Next i1

End Sub

Hope to help