That's something I've been doing very effectively for a long time. The idea is this. If you have 2 lists that you want to pick from so that you get every possible combination, you want to select 1 item from list A and combine it with every item in list B, then select the next item in list A and combine it with every item in list B.

The way to loop through a list is to use a function that generates 1,2,3,...,N,1,2,3,...,N,... This is close to what the MOD function does. It generates values 0,1,2,...N-1,0,1,2,...,N-1,...

So, ROW()-ROW(1st row) will generate 0,1,2,3,... and MOD(,N) will yield 0,1,2,...N-1,0,1,2,...,N-1,... Add 1 to that and you get the desired sequence.

Now, for list A you want to increment the index only after every item in list B has been selected, which will happen every N rows. So, INT(ROW()-ROW(1st row) / N) will jump from one integer to the next every N rows.

You can extend this concept to any number of lists A, B, C, D,...See

Generate All Permutations

Generate All Permutations
## Like this thread? Share it with others