Hi,

Let's say Column M has string type of data starting from row 2 and
correspondingly column N (starting from row 2) has some number type of data
in the following manner.

Column M Column N
Advanced Revelations 23
Macromedia Cold fusion 34
ABC BCD EF GY 45


The in column I, I want to output all the combinations of words (with
respect to spaces) in Column I (along with the original number codes of
column N to be in Column J)

My desired output would be

Column I Column J
Advanced Revelations 23
AdvancedRevelations 23
Macromedia Cold fusion 34
Macromedia Coldfusion 34
MacromediaCold fusion 34
MacromediaCold fusion 34
ABC BCD EF GY 45
ABC BCD EFGY 45
ABC BCDEF GY 45
ABCBCD EF GY 45
ABC BCDEFGY 45
ABCBCD EFGY 45
ABCBCD EFGY 45
ABCBCDEFGY 45


Basically if the original cell has 5 words, then there will be 4 spaces
among these 5 words. I want to output the 2 power 4 ( 2 ^ 4) combinations
starting from no space to space between any 2 words ( space between any 2
words is same as the original data in Column M).



Please tell me how to go about it.

I have some basic codes for splitting original word in the cell and
concatenating cells but when it comes to permutation, Iam not able to think
of how to set up the code in such a way that if there are 8 words in the
cell then there would be 2^7 combinations to be considered. Iam clueless
about how to simulate the number of permutations programmatically.

Im posting my naive code in the follow up mail to this.


Thanks a lot,
Hari
India

Hi,

Here is some code. I have left out the most important part which is the
series (?) of FOR Loops, as am not able to set up that. Since number of
words in a ell could be variable, im not sure about how to set up the for
loops.

Option Explicit

Public arrWords As Variant

Sub MakeMeMessy() ' this is the MAIN SUB

Dim SpaceArray(1) As String
SpaceArray(0) = ""
SpaceArray(1) = " "

Dim rowcount As Integer

Range("i2:j65536").Select ' This is to clear any existing data in column I
and Column N
Selection.ClearContents

Range("i2").Select

For rowcount = 2 To Range("M65536").Selection.End(xlUp).Row

Splitwords ActiveSheet.Range("m" & z).Value

For .. = .. to ...
For .. = .. to ...
For .. = .. to ...
......
...
activecell.value = MergingElementsOfArray(,,,,)




End Sub


Public Sub Splitwords(sText As String)

Dim x As Integer
Dim arrReplace As Variant

arrReplace = Array(vbTab, ":", ";", ".", ",", "-", "/", Chr(10), Chr(13))
For x = LBound(arrReplace) To UBound(arrReplace)
sText = Replace(sText, arrReplace(x), " ")
Next x

arrWords = Split(Application.WorksheetFunction.Trim(sText), " ")

End Sub

Function MergingElementsOfArray(.....) As Variant
Dim F As Integer
dim tmp as string

For F = lbound(arrowrds) to ubound(arrwords)

tmp = tmp & concatarray(F) & Spacearray(i)

'here is where the problem is .. how to permute the spacearray value such _
that some words have spaces between them while some dont. in the present _
form either words have spaces between them or no 'space between any word.

Next F

MergingElementsOfArray = UCase(Application.WorksheetFunction.Trim(tmp))

End Function


Please guide me.

Thanks a lot,
Hari
India

Hi,

If anybody has some ideas on how to set up the logic of this please do tell
me. If the previous post is unclear on what am seeking I wud try to post a
better explanation of the same.

Thanks a lot,
Hari
India