How to generate a list based on values from several columns

[KForsman]

Hi,

Thought I was somehow good at Excel but now I have no clue at all.

I need to generate a list of all possible combinations based on values from several columns.
Is it possible with normal Excel formulas or do I need VBA programming?

For instance

	A	B	C
1	Alpha	!	100
2	Beta	#	200
3	Charlie

Would generate all these possible combinations:
Alpha!100
Alpha!200
Alpha!
Alpha#100
Alpha#200
Alpha#
Alpha100
Alpha200
Alpha
Beta!100
Beta!200
Beta!
Beta#100
Beta#200
Beta#
Beta100
Beta200
Beta
Charlie!100
Charlie!200
Charlie!
Charlie#100
Charlie#200
Charlie#
Charlie100
Charlie200
Charlie

[KForsman]

And of course, how do I generate the list.

I made this by hand, but with dozens of rows and a couple of columns it quickly becomes thousands of combinations so I need to autogenerate it.

[XOR LX]

Hi,

Is your data fixed at three columns' worth, as in the example you give here (though perhaps with a variable number of rows)?

Regards

[KForsman]

Yes, the data is fixed to three columns, but as in my example the amount of values can differ.

Let's say column A can have 20 rows, B 10 rows and C 5 rows.

[XOR LX]

Thanks.

I'm a little curious as to how you're treating blank cells with regards to your expected results.

In your example, the two blank cells in B3 and C3 are seemingly still processed, and so lead to results of "Alpha", "Beta" and "Charlie" on their own.

But of course, if, as you say, "column A can have 20 rows, B 10 rows and C 5 rows", this will lead to 5 cases where only the column A entries are non-blank, and so you will end up with, in this case, 3000 entries (150 each for the 20 entries in column A) which consist of just the string in column A, not to mention numerous duplications of other concatenations.

Or, if it's the case that single entries are just to be returned once, then why do you not have any expected results such as:

!
#
100
200

?

Can you please clarify?

Regards

[KForsman]

Yes, in a case with 20/10/5 rows it means that it will generate the single value from A1 150 times (empty value in B11 * empty C6 - C20, empty B12 * empty C6-C20.....), as well as with all other values in column A.

This assumes a full "area" A1:C20.
If it is possible to remove duplicates from the list, or chose to use values A1:A20;B1:B10;C1:C5, it's great.

Single entries can be returned once "A1, A2" etc, but it always start with the value in column A.
As the area is ranging from A1:C20 it has no empty value in A, considering A has 20 values. Because of this it can never return a single ! # 100 200
on the other hand, in case A would have 19 rows, B 20 rows and C 5 rows, it would be possible to return a single value from B20 (empty A20, values in any B, empty values C6 onwards.



I just want this formula to combine values from columns A, B, C and return all possible combinations.

[XOR LX]

Still not quite sure what you want, and this is definitely better-suited to a VBA approach, but you could try something like:

=IF(ROWS($1:1)>ROWS(Rng)^COLUMNS(Rng),"",INDEX(Rng,INT(1+((ROWS($1:1)-1)/(ROWS(Rng)^2))),1)&INDEX(Rng,1+MOD(INT(1+((ROWS($1:1)-1)/(ROWS(Rng))))-1,ROWS(Rng)),2)&INDEX(Rng,1+MOD(ROWS($1:1)-1,ROWS(Rng)),3))

where Rng should be replaced with the range over columns A:C which forms the smallest rectangle encompassing your data.

Copy down as required.

Regards

[KForsman]

I finally managed to translate your formula to my local language Excel-version
I also had to replace colons with semicolons, and Rng was put with $ to A$1:C$15 as it didn't work without $.

It seems to work with an test example of 15 rows returning 3375 possible combinations.


This formula doesn't remove duplicates, but for my needs it doesn't matter.
Instead I used the Excel built-in function to remove duplicates (had to copypaste all results and format as text-only, otherwise it took several minutes)


I tried with 4 columns also and changed the last 3 to a 4 in your formula, but it didn't work this way.
On the other hand, I don't think I'm in need of a fourth column at this point.
I have to analyze how your formula works and hopefully I manage to include a fourth possible column on my own.




Thank you very much for your help!
Could never have managed this on my own.

[XOR LX]

You're welcome. It's certainly not the most flexible solution in the world, nor the most efficient, but hopefully it at least gives you something to use for now.

Cheers