Text Combinations

[Les]

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

 

[Les]

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

 

[Nigel]

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
(e-mail address removed)

 

[Joel]

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]

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.

 

[Les]

Thanks for the help Joel.
Do I need to alter the code in some way?

 

[Joel]

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]

Cheers Joel

I got it to work but it's not what I'm after.

 

[Joel]

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]

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]

I will make the minor modification a little later today. You will see the
basic stucture of my code will be identical.

 

[Les]

Thanks for the help Joel.
Appreciate it.

I will make the minor modification a little later today. You will see the
basic stucture of my code will be identical.

 

[Joel]

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]

Cheers Joel

Absolutle spot on once I had changed the variables as to how I needed them.

Regards

Les.