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