fill down of formula with the last row

[coolkris]

Dear all,

This is my 1st post in this forum.

I need to split long(300 rows) in separate cells containing 30 words each(to copy and paste in tekla)

I used the formula (=concatenate(transpose(a1:a30)& " ")) and then F9 to concatenate again.

When I fill down the formula for 31 to 60 and so on, I get a2:a31 instead of a31:60

[Pepe Le Mokko]

Perhaps =concatenate(transpose(INDEX(A1:A1000,30*(row()-1)+1):INDEX(A1:A1000,30*(row()-1)+30))&" ")

Starting in row 1

[coolkris]

Dear Pepe Le Mokko,

Thanks a lot that's exactly I needed.
I am getting little more lazy to find that { } after pressing F9 every row.
I need to concatenate again and change bracket to (). I got lot of rows like that.

Please suggest some way I can do for 1 row and drag it down.

Thanks a lot

Krish

[Pepe Le Mokko]

I don't see what you mean.
Please post a small sample sheet (no pics please)

[coolkris]

Dear sir,

Please find attached file.

[JeteMc]

Perhaps the following will help. Note that this proposal employs two helper columns (B:C) which may be moved and or hidden for aesthetic purposes, and that the original list has been shifted down one row.
Column B is populated using: =MOD(ROWS(A$1:A1),30)=0
Column C is populated using: =IF(ROW()=2,A2,IF(B1=TRUE,A2,C1&", "&A2))
The output in column G is populated using: =IFERROR(INDEX(C$2:C$314,AGGREGATE(15,6,(ROW(A$2:A$314)-ROW(A$1))/(B$2:B$314=TRUE),ROWS(A$1:A1))),"")
Let us know if you have any questions.