Can excel list the possible combinations from a range of cells

S

Syndrome

I have 15 cells with differing data in them, for example Andy in A1, Lesley
in A2, Keith in A3 etc etc.
I want to calculate and list the different combinations of the entries in a
column..... For e.g Andy
Andy, Lesley
Andy, Keith
Andy, Lesley, Keith
Lesley, Keith
 
S

Syndrome

Thanks for this.
I'm no expert in VBA though. Do I just copy this into a module and run the
macro whilst within the spreadsheet? Or is there anything else I should be
doing?

Thanks again.
 
S

Syndrome

Thanks again, I've got the macro to work. Could we alter the macro so that
the subsets are copied onto the active sheet rather than listed as a message
box? If so do you know the code required?

Thanks
 
G

Gary''s Student

Sure:

This is a replacement for Coleman's TestThis:

Sub TestThis()
Dim i As Integer
Dim A(3 To 7) As Integer
Dim B As Variant
Dim s


For i = 3 To 7
A(i) = i
Next i
B = Array("dog", "cat", "mouse", "zebra")


MsgBox ListSubsets(A)
MsgBox ListSubsets(B)
s = Split(ListSubsets(B), vbCrLf)
For i = 0 To UBound(s)
Selection.Offset(i, 0).Value = s(i)
Next
End Sub


First select a cell on the worksheet and then run the macro. The
combinations will be pasted starting at the selected cell.


I left in the MSGBOXs. You can remove these two lines if you don't need them.
 
S

Syndrome

Genius, Thank you very much.

Gary''s Student said:
Sure:

This is a replacement for Coleman's TestThis:

Sub TestThis()
Dim i As Integer
Dim A(3 To 7) As Integer
Dim B As Variant
Dim s


For i = 3 To 7
A(i) = i
Next i
B = Array("dog", "cat", "mouse", "zebra")


MsgBox ListSubsets(A)
MsgBox ListSubsets(B)
s = Split(ListSubsets(B), vbCrLf)
For i = 0 To UBound(s)
Selection.Offset(i, 0).Value = s(i)
Next
End Sub


First select a cell on the worksheet and then run the macro. The
combinations will be pasted starting at the selected cell.


I left in the MSGBOXs. You can remove these two lines if you don't need them.
Click to expand...
 
Top