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]

Hi & Welcome to the Board!

You can do this without a macro using an SQL query:

1. Have your data in col A,B,C in a fresh clean worksheet (ie contains no other data except headers such as Colour, Item, Type for A,B,C - amend as appropriate)
2. Save this spreadsheet down in any location you desire
3. On a fresh sheet within this spreadsheet (eg Sheet2), go Data ribbon>Get External Data section>From other sources icon>From microsoft query
4. In the choose data dialog that will open, select "Excel files">OK
5. Navigate to the the file location, select it and hit OK
6. The Query Wizard-Choose Columns dialog will open. Hit the Options button at the bottom which will open up another dialog (Table Options). Make sure "System Tables" is checked in the list>OK
7. Select "Sheet1$" from the Available tablesand Columns box and then click the ">" button to move the required headings into your "Columns in your query" box
8. Hit Next>Next>Next and make sure you select "View Data or edit query in MS Query">Finish
9. MS Query will open up and you want to view the SQl by View>SQL
10. You need to modify the SQl from something like the following (note the path location of the file will differ, use yours rather than mine!):
old:

SELECT `Sheet1$`.Colour, `Sheet1$`.Item, `Sheet1$`.Type
FROM `C:\Users\Richard\Documents\Spreadsheets\QTTest.xlsx`.`Sheet1$` `Sheet1$`

modify to:

SELECT T1.Colour, T2.Item, T3.Type
FROM `C:\Users\Richard\Documents\Spreadsheets\QTTest.xlsx`.`Sheet1$` T1,`C:\Users\Richard\Documents\Spreadsheets\QTTest.xlsx`.`Sheet1$` T2,`C:\Users\Richard\Documents\Spreadsheets\QTTest.xlsx`.`Sheet1$` T3

11. Having clicked OK, go File>Return Data to Excel, and choose a cell (typically A1) to return data to and click OK

That's it! You should now have your data returned to the new sheet.

 

[erik.van.geit]

EDIT: WOW, Richard, that is 2007 specific, I suppose (didn't check not try)

heiniquadrassel,
WELCOME to the Board!

You can do that using some code like this. It ran quite fluently for 65000 items (I had 100 items in 1st column, 10 in 2nd and 65 in 3rd) on my rather old machine: a few seconds. This is for 3 columns, but you get the picture...

Option Explicit
 
Sub combinations()
'Erik Van Geit
'080127
'combine all items, rows 2 to ... in columns 1 to 3
'CAVEAT: could take long to process or even run out of memory, if there are many data!
 
Dim arr(1 To 3) As Variant
Dim r(1 To 3) As Long
Dim i1 As Long
Dim i2 As Long
Dim i3 As Long
Dim result As Variant
Dim c As Long
Dim comb As Long
Dim ans As Long
Dim cnt As Long
comb = 1

    For c = 1 To 3
    r(c) = Cells(Rows.Count, c).End(xlUp).Row - 1
    arr(c) = Application.Transpose(Range(Cells(2, c), Cells(r(c) + 1, c)))
    comb = comb * r(c)
    Next c
ReDim result(1 To comb)
 
    ans = MsgBox("Ready to process " & comb & " items." & vbNewLine & "Continue?", 292, "Process?")
    If ans = vbNo Then Exit Sub
 
    For i1 = 1 To r(1)
        For i2 = 1 To r(2)
            For i3 = 1 To r(3)
            cnt = cnt + 1
            result(cnt) = arr(1)(i1) & " " & arr(2)(i2) & " " & arr(3)(i3)
            Next i3
        Next i2
    Next i1
    
    With Columns("D")
    .ClearContents
    .Cells(1, 1) = "COMBINATIONS"
    .Cells(2, 1).Resize(cnt, 1) = Application.Transpose(result)
    End With
 
End Sub

kind regards,
Erik

 

[Richard Schollar]

Hey Erik

You can do this in xl2003 and below - because of the cartesian product produced, with 3 columns you can't really have more than 40 data items in each column (assuming 4 columns the same size). It's safer in xl2007...

 

[dave3009]

Hi Richard

I tried the SQL route (purely on the ground of my own research/education in XL2003) and came up with this query, what did I do wrong? It won't return anything and I get an "Could not add the table" type error message

SELECT T1.Colour, T2.Type, T3.For
FROM `C:\Documents and Settings\Dave\My Documents\TestBooks\book4.xlsx`.`Sheet1$` T1,`C:\Documents and Settings\Dave\My Documents\TestBooks\book4.xlsx`.`Sheet1$` T2,`C:\Documents and Settings\Dave\My Documents\TestBooks\book4.xlsx`.`Sheet1$` T3

Cheers


Dave

 

[Richard Schollar]

Dave

If you're using 2003 I suspect you don't want to have an extension of .xlsx on your Excel file - see if replacing that with xls solves your problem.

 

[dave3009]

Works a charm, Thanks Richard

 

[VoG]

Off topic (sorry) - what is "cartesian product"

 

[Richard Schollar]

Off topic (sorry) - what is "cartesian product"

I just mean that if you have 3 columns of data (I quoted 4 incorrectly in my reply to Erik) of 40 data items each then the result set produced witll be:

40*40*40

which equals 64,000 combinations (so the cartesian product is the multiple of the terms).

 

[VoG]

I just mean that if you have 3 columns of data (I quoted 4 incorrectly in my reply to Erik) of 40 data items each then the result set produced witll be:

40*40*40

which equals 64,000 combinations (so the cartesian product is the multiple of the terms).

OK so you just meant the multiple - that I can understand. Thanks