Matrix of combinations

TheOrchyster54

Hi everyone,

I was wondering if anyone had any idea on how to generate a matrix where:

- 1 row is a combination

- 1 column per element possible of a combination

My input is p lists as follows:

List 1	List 2		List p
Bob	Blue		Paris
Katie	Red		London
Karen			Madrid

1 combination being one element of list 1, one element of list 2 & one element of list p. For example, "Bob, Blue, Paris" or "Bob, Blue, London" or "Karen, Red, Madrid".

If n_p is the length of the list. The matrix will give n_1*n_2*...*n_p rows and n_1+n_2+...+n_p columns

The output wished would be:

Combination	Bob	Katie	Karen	Blue	Red	...	Paris	London	Madrid
#1	1	0	0	1	0	...	1	0	0
#2	1	0	0	1	0	...	0	1	0
	...	...	...	...	...	...	...	...	...
#n_1*n_2*...*n_p	0	0	1	0	1	...	0	0	1

Thank you very much for your help,

Jean

danilang

Hi @TheOrchyster54 

Tricky little problem.  To solve it, use an iterative macro to generate all the combinations and then split and crosstab.  The first transpose makes it dynamic to handle any number of lists and elements per list

Main

Macro

Dan

Main.yxzp