Can excel list the possible combinations from a range of cells

[Guest]

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

 

[Guest]

See Coleman's response in :


http://groups.google.com/group/micr...Excel+gray+code&rnum=1&hl=en#791f529de2aaf685

 

[Guest]

See Coleman's response in :


http://groups.google.com/group/micr...Excel+gray+code&rnum=1&hl=en#791f529de2aaf685

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

You are correct.

For more info on VBA see:

http://www.cpearson.com/excel/codemods.htm

 

[Guest]

You are correct.

For more info on VBA see:

http://www.cpearson.com/excel/codemods.htm

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Genius, Thank you very much.

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.

 

[Guest]

Genius, Thank you very much.

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.