Macro for permutations/combinations?

[svs9j]

Hi,

I have a sequence of 6 strings (3 each in 2 categories)

Category Brand(B) : A, B, C
Category Product (P): D, E, F

I am trying to work out an sequence like BBBPPP using each ad from the B and P categories and have all combinations, maintaining the BBBPPP order.

Example:

ABC DEF
CBA DEF
BCA DEF
----
----
----

Is there a way I can do this in Excel where I can automatically list these combinations? I have been trying to play around with the PERMUT and COMBIN functions with no luck. I want it to output the actual sequence and not the # of iterations possible.

Any help is appreciated.

Thanks!

[stanleydgromjr]

svs9j,

See:

Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc
http://www.mydatabasesupport.com/for...binations.html

pgc01
http://www.mrexcel.com/forum/showthread.php?t=412952

Sub PermutWords()
pgc01
http://www.mrexcel.com/forum/showthread.php?t=371960

[svs9j]

Thank you stanleydgromjr

I am looking for this exactly, but instead of one word in A1, I want say 3 words in A1, A2, A3 and combination of that. How can I modify the code to do this?


Write the word in A1

Try:

Code:

Sub PermutWords()
Dim lrow As Long

PermutWords1 Range("A1"), "", lrow
End Sub

Function PermutWords1(ByVal s As String, ByVal sPermut As String, ByRef lrow As Long)
Dim l As Long

If Len(s) = 1 Then
lrow = lrow + 1
Range("B" & lrow) = sPermut & s
Else
For l = 1 To Len(s)
PermutWords1 Replace(s, Mid(s, l, 1), ""), sPermut & Mid(s, l, 1), lrow
Next l
End If
End Function

[shg]

svs, please take a few minutes to read the Forum Rules, and then edit your post to add code tags.

[martindwilson]

do you still want an answer for this?