bulk permutation of words

[heiniquadrassel]

Hi all,

i have read through all threads about permutation, and also a few others. I if I were an Excel Pro I would probably have been able to come up with a solution to my problem based on all the suggestions made, but unfortunately it is (still) all a bit too complex for me.

The problem:

I have between 2 and 5 lists of words, each list in one column:

Column A

red
green
yellow
black
blue green
pink

Column B

shirt
jumper
blouse
coat
t-shirt
skirt

Column C

for women
for men
for girls
extra large
for boys
one size fits all

Column D

etc

Note that one cell may contain several words, separated by a space.

I need to permute the cells in a way that gives me the following result:

red shirt for women
red shirt for men
red shirt for girls
red shirt extra large
red shirt for boys
red shirt one size fits all
red jumper for women
red jumper for men
...
pink skirt for boys
pink skirt one size fits all


I do not want to permute the words in a single cell like:

red shirt large extra
red size one shirt fits all


The cells may contain characters like "-", "'" or ",".

The list may be very long, but I expect the resulting list to fit into one Excel 2007 column, although if it would be possible to split up the result list into several columns just in case that would be nice.

It would be great if I could choose everytime I perform this task to permutate either only in this way:


A1&B1&C1
A1&B1&C2
A1&B1&C3
A2&B1&C1
A2&B1&C2
...

or also

A1&B1&C1
B1&C1&A1
B1&A1&C1
C1&A1&B1
...

which would obviously produce many more results and would normally not be necessary.


To put it into a nutshell I think I need a macro that concatenates each cell in column A with each cell in Column B, C, D, and E in either all possible combinations, or in all possible combinations but with keeping the original order of columns, and writes all resulting combinations in one column.



Please excuse me for being unclear but I am not used to expressing such problems in this form.

Any suggestions appreciated.

cheers,
Arian

 

[Richard Schollar]

I found various references to certain limits imposed on Application.Transpose, but I found nothing about Excel 2007. Couldn't I use looping instead of the Application.Transpose, even if it takes a bit longer?

Sorry if this doesn't make any sense, I am trying.

Cheers

Try this:

Sub Combs()
Dim i As Long, j As Long, k As Long, ws As Worksheet, wsAct As Worksheet
Dim va1, va2, va3, vaResult, lngSize As Long, lngCnt As Long
Set wsAct = ActiveSheet
With wsAct
    va1 = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
    va2 = .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
    va3 = .Range("C2:C" & .Cells(Rows.Count, "C").End(xlUp).Row)
End With
lngSize = UBound(va1, 1) * UBound(va2, 1) * UBound(va3, 1)
ReDim vaResult(1 To lngSize, 1 To 3)
lngCnt = 1
For i = 1 To UBound(va1, 1)
    For j = 1 To UBound(va2, 1)
        For k = 1 To UBound(va3, 1)
            vaResult(lngCnt, 1) = va1(i, 1): vaResult(lngCnt, 2) = va2(j, 1): vaResult(lngCnt, 3) = va3(k, 1)
            lngCnt = lngCnt + 1
        Next
    Next
Next
Set ws = Worksheets.Add
With ws
    .Range("A1:C1").Value = wsAct.Range("A1:C1").Value
    .Range("A2").Resize(lngCnt - 1, 3).Value = vaResult
End With
End Sub

Excel may well run out of memory with very large amounts of data - does assume you have header row in row1 and that the data is in 3 columns A,B,.

 

[heiniquadrassel]

Works like a charm, thank you so much Richard! This will save us quite some time & hassle in the future.

I promise to start giving back to this wonderful board as soon as I am a little bit more advanced.

Thank you all of you!