|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
September 7th, 2006, 11:55 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 
|
|
#2
September 7th, 2006, 01:49 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#3
September 7th, 2006, 01:59 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#4
September 7th, 2006, 02:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#5
September 7th, 2006, 03:40 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#6
September 7th, 2006, 05:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
|
#7
September 9th, 2006, 09:00 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
