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

 

[erik.van.geit]

Very short of time these days...
Thanks for the explanation, Richard
(no time to try out now yet)

 

[heiniquadrassel]

Wow, thank you all for your contributions. Sorry for the late reply, but this whole thing is just a side project at the moment.

I haven't tried the SQL approach yet although I certainly will, because it looks really intriguing and I want to know how exactly it works. The reason why I need to use a macro is because I want it to be one among several others that will make life easier for a few of my colleagues, who understand even less Excel than I do.

Thanks Erik for your macro, works like a charm, and it is so much faster than I expected. The only issue I have that I get error messages whenever I try to permute something which would rersult in more than 65536 rows (I use Excel 2007):

runtime error 13
type mismatch


What could be reason?

Thank you all again for sharing your knowledge that freely.

 

[Richard Schollar]

Which line of the code is highlighted when you get this error?

 

[heiniquadrassel]

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

 

[heiniquadrassel]

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

 

[Richard Schollar]

Yes it's because of the transpose (that would be my thoughts anyway) - I haven't looked at the original code yet (too busy at work) but my thoughs are that you can do this without transpose(or do it in blocks up to the transpose limit if required). If Erik doesn't post later (or someone else) I'll try and have a look tonight.

 

[tusharm]

Slick use of MS Query and the cartesian product consequence of not joining tables. {grin}

One tweak. You don't have to modify the resulting SQL. While in MS Query, add the same table (sheet) twice more. Each time Query will ask if you really want to add a table that is already in the query. Just say yes. Now, delete the 2nd and 3rd columns currently displayed. Then, add the 2nd column from the 2nd table and the 3rd column from the 3rd table. This will cause SQL to generate the cartesian product.

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.

 

[btadams]

Richard,

I'm trying to learn more about SQL capabilities in Excel. I'm trying to follow your directions but when I select the Excel file as the data source it says the file contains no visible tables. The file in question contains lists of items in columns A - C. What do I need to do? Thanks,

 

[Richard Schollar]

Thanks Tushar

btadams

Click on Options and ensure System Tables is checked (this will give you the sheets themselves to use as data tables).

 

[btadams]

Richard & Tushar,

That is very cool. Thanks,