Automate concatenation between single cell and multiple target cells

[garnerseo]

I'm trying to find out a way I can concatenate a single cell with individual target cells. I can manually do this but is there a neat way of performing the task so I concatenate each cell from the target column into a new cell each containing the source cell and target cell strings.

For example:
CELL A1 =CONCATENATE(C1," ",D1)
CELL A2 =CONCATENATE(C1," ",D2)
CELL A3 =CONCATENATE(C1," ",D3)
and so on...
Then
CELL A50 =CONCATENATE(C2," ",D1)
CELL A50 =CONCATENATE(C2," ",D2)
CELL A50 =CONCATENATE(C2," ",D3)
and so on...

[XOR LX]

Hi,

=CONCATENATE(INDIRECT("C"&ROUNDUP(ROW()/49,0))," ",D1)

Regards

Edit:

=CONCATENATE(INDIRECT("C"&ROUNDUP(ROW()/49,0))," ",INDIRECT("D"&IF(MOD(ROW(),49)=0,49,MOD(ROW(),49))))

[garnerseo]

wow fast and perfect!

I started working on the first example and was happy with that tweaking the target back to cell 1, then the update made me smile. I am automated!!

Thank you.

[Ghozi Alkatiri]

at A1 try this
=CONCATENATE(INDEX(C:C,TRUNC((ROW(A1)-1)/50)+1)," ",INDEX(D:$D,MOD(ROW(A1)-1,50)+1))

copy down

[garnerseo]

I'm spoilt for choice now.

Thanks for your help XOR LX and Ghozi Alkatiri

[XOR LX]

You're welcome.