How to find every combination of two columns of data?

**xlyfe** · 06-22-2015, 06:12 PM

Can somebody help me figure out how to find every combination possible for 2 columns of data? The tricky part here has to do with the 2nd Columns list of data. This column has to be calculated to figure every combination of itself as well. This might be much easier to explain with an example. Below is an example of what I'm looking to do.

Column A	Column B
A	Apple
B	Banana
C	Orange
D	Pear
	Watermelon
	Kiwi
	Cantaloupe
	Strawberry
	Melon

Results I'm looking for: (This example is only going to show all the results for 'A')
A, Apple
A, Banana
A, Orange
A, Pear
A, Watermelon
A, Kiwi
A, Cantaloupe
A, Strawberry
A, Melon
A, Apple, Banana
A, Apple, Orange
A, Apple, Pear
A, Apple, Watermelon
A, Apple, Kiwi
A, Apple, Cantaloupe
A, Apple, Strawberry
A, Apple, Melon
A, Apple, Banana, Orange
A, Apple, Banana, Pear
A, Apple, Banana, Watermelon
...
...
...
...
...
...
A, Apple, Banana, Orange, Watermelon, Kiwi, Cantaloupe, Strawberry, Melon

As you can see, doing this by hand is proving to be somewhat cumbersome and quite frankly, a huge waste of time that has many opportunities for human error. I'm hoping that there is somebody on here that has an easy solution for resolving this problem, because I am stumped, and my brain hurts from trying to figure it out.

I guess I should also mention that the 'Column A' data will never need to be included in the subset combination sequence. Meaning, there will never be a result of "A, B, C, Apple, Banana, Orange, Watermelon....etc.". It will always only be an single cell of data from 'Column A' preceding each combination result from 'Col B'.

Thank you in advance to whoever the certifiable genius is that can figure this out!

-Mike in Sacramento, CA.

**FlameRetired** · 06-22-2015, 07:58 PM

I'm assuming the comma separation is desired. Do I assume correctly?

Edit Will this also include pattern like

A, Banana, Orange
A, Banana, Pear
A, Banana, Watermelon

etc.

**xlyfe** · 06-22-2015, 08:10 PM

Originally Posted by FlameRetired

I'm assuming the comma separation is desired. Do I assume correctly?

Yes, that would be optimal.

**FlameRetired** · 06-22-2015, 08:12 PM

OK. BTW: My edit crossed in the mail.

Will this also include pattern like

A, Banana, Orange
A, Banana, Pear
A, Banana, Watermelon

etc.

**xlyfe** · 06-23-2015, 12:46 PM

If possible, yes. It's beyond my knowledge to figure out how to do that.

Thank you so much FlameRetired for attempting to solve this. It is greatly appreciated.

**xlyfe** · 06-25-2015, 11:54 AM

Am I allowed to 'bump' this? Since I first posted this question, I've continued to try and figure this out, but am still having no luck?

**Sam Capricci** · 06-25-2015, 12:12 PM

You certainly should bump the post if it isn't answered. Out of curiosity is a combination also A, banana, Apple, watermelon, so the same as one other just a different order?

**xlyfe** · 06-25-2015, 12:27 PM

Hi Sambo! Your response is like a breath of fresh air. Or a glimmer of light at the end of my seemingly endless black hole of a tunnel...

With that said, if you are asking me if it matters what order the Column B items are sorted in, then no, that does not matter. Or maybe I didn't understand your question. But, this problem is definitely centered solely around the combinations?

I'm looking to have each item from Col A, combined with every possible combination of the items listed in Column B. In reference to my OP, I have A, B, C & D, listed in Col A. I guess I should have only shown 'A' in that Column, because in all reality, B, C & D's results, should end up looking identical to 'A's.

Thank you so much for attempting to resolve this. If I can answer any other questions for you to help you better understand what I'm looking for, then please ask, and I will do my best to provide a clear response. Thanks again.

**shg** · 06-25-2015, 12:44 PM

There's a workbook at https://app.box.com/s/b9b9fc06beb63b9562f9 that would generate this from the list of fruits:

Row\Col	B	C
11	m	Apple Banana Orange Pear Watermelon Kiwi Cantaloupe Strawberry Melon
12	1	Apple
13	1	Banana
14	1	Orange
15	1	Pear
16	1	Watermelon
17	1	Kiwi
18	1	Cantaloupe
19	1	Strawberry
20	1	Melon
21	2	Banana Apple
22	2	Orange Apple
23	2	Orange Banana
24	2	Pear Apple
25	2	Pear Banana
26	2	Pear Orange
27	2	Watermelon Apple
28	2	Watermelon Banana
29	2	Watermelon Orange
30	2	Watermelon Pear
31	2	Kiwi Apple
32	2	Kiwi Banana
33	2	Kiwi Orange
34	2	Kiwi Pear
35	2	Kiwi Watermelon
36	2	Cantaloupe Apple
37	2	Cantaloupe Banana
38	2	Cantaloupe Orange
39	2	Cantaloupe Pear
40	2	Cantaloupe Watermelon
41	2	Cantaloupe Kiwi
42	2	Strawberry Apple
43	2	Strawberry Banana
44	2	Strawberry Orange
45	2	Strawberry Pear
46	2	Strawberry Watermelon
47	2	Strawberry Kiwi
48	2	Strawberry Cantaloupe
49	2	Melon Apple
50	2	Melon Banana
51	2	Melon Orange
52	2	Melon Pear
53	2	Melon Watermelon
54	2	Melon Kiwi
55	2	Melon Cantaloupe
56	2	Melon Strawberry
57	3	Orange Banana Apple
58	3	Pear Banana Apple
59	3	Pear Orange Apple
60	3	Pear Orange Banana
61	3	Watermelon Banana Apple
62	3	…

Then you could take that list and use the workbook at https://app.box.com/s/47b28f19d794b25511be to generate the Cartesian product of those 511 items with the "A B C D".

**xlyfe** · 06-25-2015, 12:56 PM

Wow! Thanks SHG. This seems to pretty close to what I was looking for except that it seems to only apply to single word results. In my scenario, there will be many times where the item listed in Column B could have more than one word in it's cell. For example purposes only, one of the cells might say... "Big Red Apple", or "Small Green Apple". And sometimes it will say just... "Apple". Which is why I was hoping to separate the combo results with comma's for clarity when other people are viewing this.

But, I really do appreciate you sharing that workbook with me SHG. I will definitely be looking into the macros written in there to see if I can learn anything that might help me.

----------------------

I just looked at that Cartesian product, and I have to apologize. That looks very promising. I'm going to see what I can do with that.

**shg** · 06-25-2015, 12:58 PM

You're welcome, good luck.

**FlameRetired** · 06-25-2015, 01:01 PM

Originally Posted by mikerodrigueziii

I'm looking to have each item from Col A, combined with every possible combination of the items listed in Column B. In reference to my OP, I have A, B, C & D, listed in Col A. I guess I should have only shown 'A' in that Column, because in all reality, B, C & D's results, should end up looking identical to 'A's.

Thank you for clarifying that part.

Sorry I didn't get back to you. I was away for a while. Still not coming up with anything that does the job.

**xlyfe** · 06-25-2015, 01:14 PM

Originally Posted by FlameRetired

Thank you for clarifying that part.

Sorry I didn't get back to you. I was away for a while. Still not coming up with anything that does the job.

That's okay Flame. I completely understand. We all have our personal lives to attend to, and I wouldn't expect you to put some random guy from Sacramento above that. I'm grateful for any and all clues that I can receive from you geniuses. Even if I don't get exactly what I'm looking for, I'm pretty good about piecing together "similar/close" solutions until I can get what I'm looking for.

In response to your second comment, I haven't found what I'm looking for just yet. Though, SHG did just share with me a workbook that might be very helpful to me. I'm currently playing around with that Cartesian Product workbook to see if it will do what I'm looking for. If it doesn't, I may just break down the formulas and see if I can rebuild them to work. So, as of this moment, no, I have not found a solution... yet.

**FlameRetired** · 07-03-2015, 11:41 PM

Whew! I must say this was much more challenging than it first appeared.

Some notes are in order.
I named the ranges of fruits and the alphabetic data. They are Fruit_Stock and Alpha. I appended “ “ to the ends of both to accommodate the last concatenation/SUBSTITUTE step in column A.

There is a sheet SampleOutput that I used as a reference so I could keep the patterns straight in my mind. It has no function beyond that so delete at will.

Columns N:V are where I ‘dumbed out’ the indexing algorithm. They also have no function beyond that. I left them there in case they were helpful somehow.

There are two helper rows …. C1:Q1 and C2:Q2. They are extended beyond what’s needed in order to accommodate more (or less) than 9 rows of “Fruits”.

The formula for the repeating alphabet data in column B is

Formula:

Please Login or Register  to view this content.

The formula for the fruits is parsed in columns C:K. It is:

Formula:

Please Login or Register  to view this content.

I put line breaks in it hoping to make it more readable.

The file is attached.

Now I am going to follow the links shg posted and find out what a Cartesian product is ....

LOL.