How many combinations?

Hi,

Does this help

VBA Noob

Hi,


=OFFSET($A$1,MOD(INT((ROW(A1)-1)/COUNTA($A$1:$A$3)),3),0)&" "&INDEX($B$1:$B$3,MATCH(OFFSET($A$1,MOD((ROW(A1)-1),COUNTA($A$1:$A$3)),0),$A$1:$A$3,0))

It uses the offset function. I set A1 as the starting point then using other formulas to generate numbers to change the offset.

e.g The below returns A2. 1 column Down and 0 columns across

=OFFSET($A$1,1,0)

This part returns 000 then 111 then 222

=MOD(INT((ROW(A1)-1)/COUNTA($A$1:$A$3)),3)

So together it looks at A1,A2,A3. Change the if you had 4 condtions just change the bold. Note COUNTA($A$1:$A$3) is another way to return 3

=OFFSET($A$1,MOD(INT((ROW(A1)-1)/COUNTA($A$1:$A$3)),3),0)

The 2nd part

This returns 0,1,2

=MOD((ROW(A1)-1),COUNTA($A$1:$A$3))

Then using the index function is looks at offset of A1 and returns B1,B2 etc

INDEX($B$1:$B$3,MATCH(OFFSET($A$1,MOD((ROW(A1)-1),COUNTA($A$1:$A$3)),0),$A$1:$A$3,0))

HTH

VBA Noob