We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

LibreOffice Calc move every nth row

asked 2019-12-11 12:45:38 +0200

johnbrandonsmith gravatar image

updated 2020-08-07 02:51:09 +0200

Alex Kemp gravatar image


I have a text document with every line in the A column. I want to move every second cell into the B column one cell above, and then delete the empty row. Exactly how do I do this? I am not familiar with using LibreOffice Calc, so please explain it to me with detailed instructions. Thank you.

Before image description

After image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-12-11 18:03:03 +0200

Opaque gravatar image

updated 2019-12-11 18:12:07 +0200


another solution:

  • Put the following formula to cell B1: =OFFSET($A$1,(COLUMN()-1)+(ROW()-1)*2-1,0)
  • Drag the formula to cell C1 (or just copy)
  • Select cells B1:C1
  • Drag copy down to row 5 (now cells B1 to C5 should show what you want to achieve; attached sample file is at this stage)
  • Select columns B:C
  • Perform Data -> Calculate -> Formula to Value
  • Delete column A

See the following sample file: C:\fakepathArrangeEveryNth.ods

Hope that helps.

If the answer is correct or helped you to solve your problem, please click the check mark (✔) next to the answer.

edit flag offensive delete link more



This is very good. Thanks for the help. I have one additional question. What would the formula be if you had say 5 rows of data at a time that you wanted to move into adjacent columns? The same thing, but instead of two.

johnbrandonsmith gravatar imagejohnbrandonsmith ( 2019-12-11 23:08:55 +0200 )edit

:-) should have pointed out where the N in my formula is:

N=2: =OFFSET($A$1,(COLUMN()-1)+(ROW()-1)*2-1,0)   
N=3: =OFFSET($A$1,(COLUMN()-1)+(ROW()-1)*3-1,0) 
N=4: =OFFSET($A$1,(COLUMN()-1)+(ROW()-1)*4-1,0)  
N=5: =OFFSET($A$1,(COLUMN()-1)+(ROW()-1)*5-1,0)
Opaque gravatar imageOpaque ( 2019-12-12 01:48:03 +0200 )edit

Very helpful. Thanks again.

johnbrandonsmith gravatar imagejohnbrandonsmith ( 2019-12-12 06:25:00 +0200 )edit

Thank you so much!

baziliscus gravatar imagebaziliscus ( 2021-03-10 16:27:06 +0200 )edit

answered 2019-12-11 15:37:07 +0200

Grantler gravatar image

updated 2019-12-11 15:44:03 +0200

Insert a further row in the beginning of the sheet. Write in A1 Content and in B1 Number.

Number column B this way: 1-2-1-2-1-2 or A-B-A-B... downwards. Do this: Insert in B2 A and in B3 B. Select cells B2 and B3 and cut the content out (CTRL+X). Then select B2 to B10 (or more) and insert clipboard. It will have the form of A-B-A-B-A... or 1-2-1-2-1-2 ...

Select A1 and B1 and (Data menu) insert AutoFilter.

B1 contains filters (1 and 2 or A and B). Select 1 (or A) in filter. Copy values of column A. Create a new sheet and insert clipboard in A1 (or B1). Go back to first sheet and select 2 (or B) in filter. Copy and paste into sheet 2.

Probably there's a more elegant solution?

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-12-11 12:45:38 +0200

Seen: 2,781 times

Last updated: Dec 11 '19