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

 

[billszysz]

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.

This will be more than 35 millions combinations. Are you sure is it what you want? Excel has only about 1 milion rows.
Maybe you want random combination of these words (50 or 100)?

Check the file on my google drive

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

Regards

 

[L. Howard]

This will do 1 million (+ 12) rows of the first 10 rows of the examples you posted.

Those 10 rows are already set up in this link, just click the DO button.

Takes several seconds, with final results in column H.


https://www.dropbox.com/s/hjqe21x8p...tion with CONCATENATE 6 Columns Drop Box.xlsm

Regards,
Howard