Array Combinations with a Twist

[Ryan Sheehy]

I'm trying to rack my brain over this little problem...

I have an array that contains words from a string. As an example, if a
string has the following (without the quotes): "The quick brown fox"
the array would contain only the elements (without the quotes) "The",
"quick", "brown", "fox".

What I would like to do is to output all possible combinations of
those 4 array elements as ONE string (with the spaces returned)
without repeating the same word twice in that string. To continue on
with our example... some of the results would be: "The fox brown
quick", or, "brown The fox quick" etc...

I know of the Join(x, " ") function that can help me stick the array
elements back together as one string with spaces, but how do I do the
combination process?

One last important aspect: The number of words in a string (the
quantity of elements in the array) can be any length, but generally
there is a minimum of at least 2 words, and a maximum of 10.

Any help greatly appreciated.


Ryan

 

[BrianB]

Only with code AFAIK. Here is the 4 word set :-


Code
-------------------

'=============================================
Option Base 1
'-----------------------------------------------
Sub test()
Dim MyArray As Variant
Dim Mystring As String
MyArray = Array("The", "quick", "brown", "fox")
Dim ToRow As Long
'-------------------------------------
ToRow = 1
For c1 = 1 To 4
For c2 = 1 To 4
For c3 = 1 To 4
For c4 = 1 To 4
'-----------------------------------------------------------------
If Not (c1 = c2 Or c1 = c3 Or c1 = c4 _
Or c2 = c3 Or c2 = c4 _
Or c3 = c4) Then
Mystring = MyArray(c1) & MyArray(c2) & MyArray(c3) & MyArray(c4)
ActiveSheet.Cells(ToRow, 1).Value = Mystring
ToRow = ToRow + 1
End If
'-------------------------------------------------------------
Next
Next
Next
Next
End Sub
'==============================================

 

[Alan Hutchins]

Your first answer from Brian was excellent.

However, as you will see, there are 24 possible
combinations from 4 members.

Using the Excel function PERMUT, you will find out
that if you have 1 row with 10 words, there are a
possible 3,628,800 combinations
which means taht it would need 56 worksheets just to
display
the combinations possible for this 1 line.

Hope this isn't too gloomy, but I couldn't believe it when
I saw the number of combinations ..........