Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Hello
I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
"Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance Sorry, a little confusing, if you were to use 5 letters a, b, c, d, e then there would only be one remaining combination possible fg,h, i, j. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
I always use recursive algorithms to perform this task. Use code below.
Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Hi Nigel
Thanks for taking the time to reply. To my mind you are talking about permutations rather than combinations. Please correct me if I'm wrong. This is what I'm trying to say in the follow up post. If I use c, d, e, f, g then I'm left with a, b, h, i, j. The order they are in is irrelevant. Similarly if I specify 4 letters to be used it returns a list of all the possible combinations of 4 letters from the list of 10 and also the 6 letters that were not used in the 4 letter combination. Hope this is a bit clearer. Regards Les. "Nigel" wrote: Hi Les Are you asking to list all combinations of the two sets; the chosen group and the rest in the worksheet in two columns? If you chose all 10 you would overflow the sheet with over 3.6 million rows. So I am assuming the choice would be 9 or less? Not sure I understand your supplementary post, as if you chose A to E; this would result in 120 combinations; the remainder F-J would also have 120 Finally how would you like to choose the set? -- Regards, Nigel "Les" wrote in message ... "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance Sorry, a little confusing, if you were to use 5 letters a, b, c, d, e then there would only be one remaining combination possible fg,h, i, j. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Thanks for the help Joel.
Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Just modify the variable InStrings = Array("A", "B", "C"). My code isn't
reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Cheers Joel
I got it to work but it's not what I'm after. "Joel" wrote: Just modify the variable InStrings = Array("A", "B", "C"). My code isn't reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
the code can be easily modified. Give me an exmple of what you are calling a
combination. the are a few ways of generating combinations, but the code is very similar to the code I posted. If you add more string into Instrings (Array("A", "B", "C","D","E") it will generate longer patters. You can also have multiple length strings Array("BOB", "HARRY", "DICK"). Output can also be modified easily to change depending on your needs. Some people want one character to 3 characters as part of the combinations a ab ba abc acb bac bca cab cba "Les" wrote: Cheers Joel I got it to work but it's not what I'm after. "Joel" wrote: Just modify the variable InStrings = Array("A", "B", "C"). My code isn't reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Thanks for helping Joel.
What should happen something along the lines of this: The code should take the values A, B, C, D, E, F, G, H, I, J from the cells A1 - A10 or A1 - J1, it doesn't matter. It should then produce a final table (possibly in sheet 2) that shows all the possible combinations, not permutations, of letters used versus letters remaining. e.g. 3 letters used: Row 1 A,B,C D,E,F,G,H,I,J Row 2 A,B,D C,E,F,G,H,I,J Row 3 A,B,E C,D,F,G,H,I,J etc It should run from 1 letter used to all 10 letters used. e.g. 10 letters used would only return 1 record - A,B,C,D,E,F,G,H,I,J Regards Les. "Joel" wrote: the code can be easily modified. Give me an exmple of what you are calling a combination. the are a few ways of generating combinations, but the code is very similar to the code I posted. If you add more string into Instrings (Array("A", "B", "C","D","E") it will generate longer patters. You can also have multiple length strings Array("BOB", "HARRY", "DICK"). Output can also be modified easily to change depending on your needs. Some people want one character to 3 characters as part of the combinations a ab ba abc acb bac bca cab cba "Les" wrote: Cheers Joel I got it to work but it's not what I'm after. "Joel" wrote: Just modify the variable InStrings = Array("A", "B", "C"). My code isn't reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
I will make the minor modification a little later today. You will see the
basic stucture of my code will be identical. "Les" wrote: Thanks for helping Joel. What should happen something along the lines of this: The code should take the values A, B, C, D, E, F, G, H, I, J from the cells A1 - A10 or A1 - J1, it doesn't matter. It should then produce a final table (possibly in sheet 2) that shows all the possible combinations, not permutations, of letters used versus letters remaining. e.g. 3 letters used: Row 1 A,B,C D,E,F,G,H,I,J Row 2 A,B,D C,E,F,G,H,I,J Row 3 A,B,E C,D,F,G,H,I,J etc It should run from 1 letter used to all 10 letters used. e.g. 10 letters used would only return 1 record - A,B,C,D,E,F,G,H,I,J Regards Les. "Joel" wrote: the code can be easily modified. Give me an exmple of what you are calling a combination. the are a few ways of generating combinations, but the code is very similar to the code I posted. If you add more string into Instrings (Array("A", "B", "C","D","E") it will generate longer patters. You can also have multiple length strings Array("BOB", "HARRY", "DICK"). Output can also be modified easily to change depending on your needs. Some people want one character to 3 characters as part of the combinations a ab ba abc acb bac bca cab cba "Les" wrote: Cheers Joel I got it to work but it's not what I'm after. "Joel" wrote: Just modify the variable InStrings = Array("A", "B", "C"). My code isn't reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Thanks for the help Joel.
Appreciate it. "Joel" wrote: I will make the minor modification a little later today. You will see the basic stucture of my code will be identical. "Les" wrote: Thanks for helping Joel. What should happen something along the lines of this: The code should take the values A, B, C, D, E, F, G, H, I, J from the cells A1 - A10 or A1 - J1, it doesn't matter. It should then produce a final table (possibly in sheet 2) that shows all the possible combinations, not permutations, of letters used versus letters remaining. e.g. 3 letters used: Row 1 A,B,C D,E,F,G,H,I,J Row 2 A,B,D C,E,F,G,H,I,J Row 3 A,B,E C,D,F,G,H,I,J etc It should run from 1 letter used to all 10 letters used. e.g. 10 letters used would only return 1 record - A,B,C,D,E,F,G,H,I,J Regards Les. "Joel" wrote: the code can be easily modified. Give me an exmple of what you are calling a combination. the are a few ways of generating combinations, but the code is very similar to the code I posted. If you add more string into Instrings (Array("A", "B", "C","D","E") it will generate longer patters. You can also have multiple length strings Array("BOB", "HARRY", "DICK"). Output can also be modified easily to change depending on your needs. Some people want one character to 3 characters as part of the combinations a ab ba abc acb bac bca cab cba "Les" wrote: Cheers Joel I got it to work but it's not what I'm after. "Joel" wrote: Just modify the variable InStrings = Array("A", "B", "C"). My code isn't reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Try this
Adjust these variables as necessary ------------------------------------------------------- InStrings = Array("A", "B", "C", "D", "E") ComboLen = 3 ----------------------------------------------------- Public InStrings Public combo Public RowCount Public ComboLen Sub combinations() InStrings = Array("A", "B", "C", "D", "E") Length = UBound(InStrings) + 1 Level = 1 RowCount = 1 ComboLen = 3 ReDim combo(ComboLen) Position = 0 Call recursive(Level, Position) End Sub Sub recursive(ByVal Level As Integer, ByVal Position As Integer) Length = UBound(InStrings) + 1 For i = Position To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '124 '125 '234 '235 '245 '345 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = ComboLen Then For j = 0 To (ComboLen - 1) If j = 0 Then ComboString = InStrings(combo(j)) Else ComboString = ComboString & "," & InStrings(combo(j)) End If Next j 'make not string Notcombo = "" For j = 0 To (Length - 1) found = False For k = 0 To (ComboLen - 1) If j = combo(k) Then found = True Exit For End If Next k If found = False Then If Len(Notcombo) = 0 Then Notcombo = InStrings(j) Else Notcombo = Notcombo & "," & InStrings(j) End If End If Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString Sheets("Sheet2").Range("B" & RowCount) = Notcombo RowCount = RowCount + 1 Else Call recursive(Level + 1, i) End If End If Next i End Sub "Les" wrote: Thanks for the help Joel. Appreciate it. "Joel" wrote: I will make the minor modification a little later today. You will see the basic stucture of my code will be identical. "Les" wrote: Thanks for helping Joel. What should happen something along the lines of this: The code should take the values A, B, C, D, E, F, G, H, I, J from the cells A1 - A10 or A1 - J1, it doesn't matter. It should then produce a final table (possibly in sheet 2) that shows all the possible combinations, not permutations, of letters used versus letters remaining. e.g. 3 letters used: Row 1 A,B,C D,E,F,G,H,I,J Row 2 A,B,D C,E,F,G,H,I,J Row 3 A,B,E C,D,F,G,H,I,J etc It should run from 1 letter used to all 10 letters used. e.g. 10 letters used would only return 1 record - A,B,C,D,E,F,G,H,I,J Regards Les. "Joel" wrote: the code can be easily modified. Give me an exmple of what you are calling a combination. the are a few ways of generating combinations, but the code is very similar to the code I posted. If you add more string into Instrings (Array("A", "B", "C","D","E") it will generate longer patters. You can also have multiple length strings Array("BOB", "HARRY", "DICK"). Output can also be modified easily to change depending on your needs. Some people want one character to 3 characters as part of the combinations a ab ba abc acb bac bca cab cba "Les" wrote: Cheers Joel I got it to work but it's not what I'm after. "Joel" wrote: Just modify the variable InStrings = Array("A", "B", "C"). My code isn't reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text Combinations
Cheers Joel
Absolutle spot on once I had changed the variables as to how I needed them. Regards Les. "Joel" wrote: Try this Adjust these variables as necessary ------------------------------------------------------- InStrings = Array("A", "B", "C", "D", "E") ComboLen = 3 ----------------------------------------------------- Public InStrings Public combo Public RowCount Public ComboLen Sub combinations() InStrings = Array("A", "B", "C", "D", "E") Length = UBound(InStrings) + 1 Level = 1 RowCount = 1 ComboLen = 3 ReDim combo(ComboLen) Position = 0 Call recursive(Level, Position) End Sub Sub recursive(ByVal Level As Integer, ByVal Position As Integer) Length = UBound(InStrings) + 1 For i = Position To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '124 '125 '234 '235 '245 '345 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = ComboLen Then For j = 0 To (ComboLen - 1) If j = 0 Then ComboString = InStrings(combo(j)) Else ComboString = ComboString & "," & InStrings(combo(j)) End If Next j 'make not string Notcombo = "" For j = 0 To (Length - 1) found = False For k = 0 To (ComboLen - 1) If j = combo(k) Then found = True Exit For End If Next k If found = False Then If Len(Notcombo) = 0 Then Notcombo = InStrings(j) Else Notcombo = Notcombo & "," & InStrings(j) End If End If Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString Sheets("Sheet2").Range("B" & RowCount) = Notcombo RowCount = RowCount + 1 Else Call recursive(Level + 1, i) End If End If Next i End Sub "Les" wrote: Thanks for the help Joel. Appreciate it. "Joel" wrote: I will make the minor modification a little later today. You will see the basic stucture of my code will be identical. "Les" wrote: Thanks for helping Joel. What should happen something along the lines of this: The code should take the values A, B, C, D, E, F, G, H, I, J from the cells A1 - A10 or A1 - J1, it doesn't matter. It should then produce a final table (possibly in sheet 2) that shows all the possible combinations, not permutations, of letters used versus letters remaining. e.g. 3 letters used: Row 1 A,B,C D,E,F,G,H,I,J Row 2 A,B,D C,E,F,G,H,I,J Row 3 A,B,E C,D,F,G,H,I,J etc It should run from 1 letter used to all 10 letters used. e.g. 10 letters used would only return 1 record - A,B,C,D,E,F,G,H,I,J Regards Les. "Joel" wrote: the code can be easily modified. Give me an exmple of what you are calling a combination. the are a few ways of generating combinations, but the code is very similar to the code I posted. If you add more string into Instrings (Array("A", "B", "C","D","E") it will generate longer patters. You can also have multiple length strings Array("BOB", "HARRY", "DICK"). Output can also be modified easily to change depending on your needs. Some people want one character to 3 characters as part of the combinations a ab ba abc acb bac bca cab cba "Les" wrote: Cheers Joel I got it to work but it's not what I'm after. "Joel" wrote: Just modify the variable InStrings = Array("A", "B", "C"). My code isn't reading data from the worksheets. The code is using InStrings. You have to load InStrings before calling the macro. "Les" wrote: Thanks for the help Joel. Do I need to alter the code in some way? "Joel" wrote: I always use recursive algorithms to perform this task. Use code below. Changing Instring to any legth or any strings of character will produce different combinations Public InStrings Public combo Public RowCount Sub combinations() InStrings = Array("A", "B", "C") Length = UBound(InStrings) + 1 ReDim combo(Length) Level = 1 RowCount = 1 Call recursive(Level) End Sub Sub recursive(ByVal Level As Integer) Length = UBound(InStrings) + 1 For i = 0 To (Length - 1) 'for combinations check if item already entered found = False For j = 0 To (Level - 2) 'combo is a count of the combinations,not the actual data '123 '132 '213 '231 '312 '321 'data is actually in InStrings If combo(j) = i Then found = True Exit For End If Next j If found = False Then combo(Level - 1) = i If Level = Length Then ComboString = "" For j = 0 To (Length - 1) ComboString = ComboString & InStrings(combo(j)) Next j Sheets("Sheet2").Range("A" & RowCount) = ComboString RowCount = RowCount + 1 Else Call recursive(Level + 1) End If End If Next i End Sub "Les" wrote: Hello I have the letters A - J (10 in total). There is 1 letter in each cell from A1 to A10 or A1 to J1 if you prefer. I'm trying to produce a list of combinations, not permutations, for however many characters I use and what the remaining combination of characters are. e.g. I want to use 5 characters a - e, what are all possible combinations of those 5 charcaters and what are possible combinations of the remaining characters. another e.g. I want to use 3 characters c - e, what are the combinations of those 3 and then the remaining 7 Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing various combinations of text and returning only a number inexcel | Excel Discussion (Misc queries) | |||
combinations | Excel Discussion (Misc queries) | |||
Problems: rounding & formatting Text/# combinations | Excel Discussion (Misc queries) | |||
Problems: rounding & formatting Text/# combinations | Excel Discussion (Misc queries) | |||
text combinations | Excel Programming |