Get all possible unique cell combinations from 6 columns (multiple rows) displayed into the 7th column

[raztaz]

Hello all,

I have a table with 6 columns (A to F) and multiple rows each. The cells contain words. Taking any non-empty cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all possible unique sentences in column G.

The number of rows is different for each column. A successful result in column G must include one non-empty cell from each column (A to F) and results must not repeat.

I don't know how to create and apply macros, so I tried to use a concatenation formula. However, in this case I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of just one cell's row at a time.

Here is an example for easier understanding:

(image url: imgur: the simple image sharer)

Thank you in advance for any help!

Raz

 

[raztaz]

I just realized that I may end up with "a bit" more complete sentences than my PC could handle or that I could ever use... in the millions. So, a way to limit the solutions to 10 thousand (or thereabout) would be really helpful also.

 

[L. Howard]

Hi raztaz,

This may work for you. Looks a bit 'busy' but is fairly easy to use.

https://www.dropbox.com/s/8cddxkgsp3sq7fn/WordSmith Sentence Maker Drop Box.xlsm

The sheet itself:

Has six colored sections consisting of six columns.

Each color is called a Data Set.

Between the colored columns in each Data Set is a column of random numbers produced by code each time you run the code..

Put (type in) a word in each colored cell in each section (a couple of words if necessary in a cell) across a row.

If you fill all six Data Sets completely, it will produce 12,000 sentences.

There is code to remove duplicates.

As the workbook is when you open it, all the Data sets are not completed nor are all the column of some of the Data Sets filled.

If you only use five columns in a Data Set row, then that sentence will be shorter than a row using all six.

There is a button in the A1 area labeled Write. Just click on it as is and you will get about 4000 sentences with the current data in the Data Sets.

I'm not sure the random aspect of the sheet will exactly fit your random expectations.

Note the code is by Claus, a major contributor in the MS Excel Public Forum.

I helped with a minute portion of the code, but would be not be able to adjust the code with much confidence.

Regards,
Howard

 

[raztaz]

Thanks for the effort, however, it doesn't work for me. I cleared all data sets, put my data in all 6 orange sections (21 of my keywords didn't fit in) and then clicked write. I'm not sure what the text in columns AE and AG are, but they stayed just as they were. I scrolled further to column AQ and I only found 20 sentences created and 1/3 of them were missing the word in the last column.

 

[L. Howard]

How about posting the words here and I will give it a go.

Not a screen shot, just copy from the Excel sheet and post here so I can copy.

Howard

 

[raztaz]

Thanks for your good intentions, but I can't post the words here. Also, I will need to use this constantly.

Raz

 

[billszysz]

Hi raztaz

A few weeks ago I showed a solution to the same problem but on polish sites.
If you want you can download this workbook below and change it for your data set.
(workbook has a two solutions - solution based on RANDBETWEEN and PivotTable (for fun but it works) and another one)

https://drive.google.com/file/d/0B6UlMk8OzUrxNFV5TEhaMEl0SVU/edit?usp=sharing

Regards

 

[raztaz]

Hi billszysz,

I downloaded and opened the file, but it's not obvious to me where to place my data set. I need to place 9 cells in col A, 17 cells in col B, 26 cells in col C, 25 cells in col D, 21 cells in col E and 17 cells in col F. Where exactly do I paste my columns?

Thanks a lot!

Raz

 

[raztaz]

How about posting the words here and I will give it a go.

Not a screen shot, just copy from the Excel sheet and post here so I can copy.

Howard

Howard, here is a similar file, but with demo content instead of the actual words:
https://drive.google.com/file/d/0BxzgPV7LtOI0aV9Va2pmWkh4Ujg/edit?usp=sharing

Thanks!

Raz

 

[raztaz]

Someone has helped me on another forum. If anyone wants the solution, it's a visual basic script. Details here: Get all unique combinations from 6 columns (multiple rows) into the 7th column

Thanks all!

Raz