Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Combinations with a Twist
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Combinations with a Twist
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 '============================================== ------------------- -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Combinations with a Twist
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 .......... -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum with a Twist Q | Excel Worksheet Functions | |||
Sum with a Twist Q | Excel Worksheet Functions | |||
If but with a twist:( | New Users to Excel | |||
Sum with a twist | Excel Discussion (Misc queries) | |||
Return a cell value based on specific combinations of cells in an array | Excel Worksheet Functions |