| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| auto updating list | Excel Worksheet Functions | |||
| Excel auto filtering to find a range of dates in a list | Excel Discussion (Misc queries) | |||
| How do I get Excel to display a range of cells with a check box? | Excel Discussion (Misc queries) | |||
| Excel List range, filter arrows disappeared | Excel Discussion (Misc queries) | |||
Linear Mode
