VBA to come up with all the combinations of two columns of data

Question

I have two columns of data (column A, column B) and I want to list all the combinations of these in columns C and D. I.e. If column A has 5 numbers in a list and column B has 3 numbers in a list I should have 15 combinations listed in columns C and D. This is just an example the length of the data in columns A and B change dynamically.

I am pretty new to VBA, so a simple step by step guide would be appreciated.

Answer 1

VBA is not required for this.

In C1 enter:

=INDEX(A:A,ROUNDUP(ROW()/COUNT(B:B),0))

and copy down. In D1 enter:

=INDEX(B:B,MOD(ROW()-1,COUNT(B:B))+1)

and copy down.

You can add or remove items from either list. You can use numbers or text values in either list.

EDIT#1

To remove the unnecessary zeros at the bottom of columns C and D, In C1 use:

 =IF(ROW()>COUNT(A:A)*COUNT(B:B),"",INDEX(A:A,ROUNDUP(ROW()/COUNT(B:B),0)))

before the copy-down and in D1 use:

=IF(ROW()>COUNT(A:A)*COUNT(B:B),"",INDEX(B:B,MOD(ROW()-1,COUNT(B:B))+1))

This is based on knowing that there can be only Na X Nb combinations.