Macro to Concatenate two ranges with all possible combinations

[jaykilleen]

Hi, I am new to VBA but going through the lessons now. For the moment I have a problem I cannot solve.


On Sheet 1:


A B

1 a

2 b

3 c

4 d

5 e


I want to be able to run a macro to concatenate all values in Column A with all values in column B separated by a space.


So I will get column C as:


1 a

1 b

1 c

1 d

1 e

2 a

2 b

2 c

etc

etc

etc


In the future the ranges in column A and B will grow or I might require a third column C to be added to the concatenation.


Does anyone have any idea how to do this?

 

[Hui]

In C1: =OFFSET($A$1,INT((ROW()-1)/COUNTA(B:B)),0)&OFFSET($B$1,MOD(ROW()-1,COUNTA(A:A)),0)

Copy down to C25 (5 x 5)

 

[jaykilleen]

Hi Hui

Thanks for that, I will give that a try for now. There is already a limitation around number of values in each row. Ie if column A goes up to 10 and B only goes to 5 then 6,7,8,9,10 will be concatenated against blanks. Its pretty cool how you have done it, not that I really understand