Can excel list the possible combinations from a range of cells
 

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

Can excel list the possible combinations from a range of cells
 

See Coleman's response in :


http://groups.google.com/group/micro...529de2aaf 685



--
Gary''s Student


"Syndrome" wrote:

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

Can excel list the possible combinations from a range of cells
 

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.

"Gary''s Student" wrote:

See Coleman's response in :


http://groups.google.com/group/micro...529de2aaf 685



--
Gary''s Student


"Syndrome" wrote:

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

Can excel list the possible combinations from a range of cells
 

You are correct.

For more info on VBA see:

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

--
Gary''s Student


"Syndrome" wrote:

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.

"Gary''s Student" wrote:

See Coleman's response in :


http://groups.google.com/group/micro...529de2aaf 685



--
Gary''s Student


"Syndrome" wrote:

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

Can excel list the possible combinations from a range of cells
 

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

"Gary''s Student" wrote:

You are correct.

For more info on VBA see:

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

--
Gary''s Student


"Syndrome" wrote:

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.

"Gary''s Student" wrote:

See Coleman's response in :


http://groups.google.com/group/micro...529de2aaf 685



--
Gary''s Student


"Syndrome" wrote:

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

Can excel list the possible combinations from a range of cells
 

Su

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.
--
Gary''s Student


"Syndrome" wrote:

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

"Gary''s Student" wrote:

You are correct.

For more info on VBA see:

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

--
Gary''s Student


"Syndrome" wrote:

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.

"Gary''s Student" wrote:

See Coleman's response in :


http://groups.google.com/group/micro...529de2aaf 685



--
Gary''s Student


"Syndrome" wrote:

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

Can excel list the possible combinations from a range of cells
 

Genius, Thank you very much.

"Gary''s Student" wrote:

Su

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.
--
Gary''s Student


"Syndrome" wrote:

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

"Gary''s Student" wrote:

You are correct.

For more info on VBA see:

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

--
Gary''s Student


"Syndrome" wrote:

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.

"Gary''s Student" wrote:

See Coleman's response in :


http://groups.google.com/group/micro...529de2aaf 685



--
Gary''s Student


"Syndrome" wrote:

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