Concatenate Cells based on Dynamic criteria

[Need_help_in]

I am looking to write a formula that can concatenate cell A$1$ (absolute) & B1: B245 (dynamic)and then A$2$- B1:B245 and then so on.


So for example, I need to concatenate cell A$1$ & B1, and then A$1$ & B2, all the way up until B245, and then I need to restart the sequence with A2 and B1.


Can someone assist me in creating a formula to preform this concatenation, or will this need to be done with VBA? Any help is sincerely appreciated! Thanks!

 

[mikerickson]

If it is always 245 rows from B
=INDEX(A:A, INT(ROW(A245)/245),1)&INDEX(B:B, MOD(ROW(A245),245)+1, 1)