Forum Discussion

Copper Contributor

Jun 16, 2020

Solved

How can i sum up a Textchain in Blocks of 4 and pull down the formula?

Hi, I would like to display the text from a column (always 4 rows) in a cell and continue this in the cell below. E.g: Show the text from A1 to A4 in B1, A5 to A8 in B2, A9 to A12 in B3 etc. ...

excel

SergeiBaklan
Jun 17, 2020
math_g

That could be in B1

=CONCAT(INDEX(A:A,(ROW()-1)*4+1):INDEX(A:A,ROW()*4))

and drag it down

SergeiBaklan

Diamond Contributor

Jun 17, 2020

math_g

That could be in B1

=CONCAT(INDEX(A:A,(ROW()-1)*4+1):INDEX(A:A,ROW()*4))

and drag it down

math_g
Copper Contributor
Jun 17, 2020
This works pretty well but how do i have to modify the formular if i want to start in row 6?

E.g. A6 to A9 displayed in B6
A10 to A13 in B7
and so on...
- SergeiBaklan
  Diamond Contributor
  Jun 17, 2020
  math_g
  
  If you start from another row, we may keep that starting row as parameter in the cell (let say 6 in C1), formula could be
  
  =CONCAT(INDEX(A:A,(ROW()-$C$1)*4+$C$1):INDEX(A:A,(ROW()-$C$1+1)*4+$C$1-1))
  
  or like