How to copy every nth row to a new column? [solved]

lampo · Newbie Joined: 10 Oct 2011 Posts: 4

Hi!

My problem is, that i have 7000 rows of data from laboratory and I should squeeze this data pack so that i take only every 5th row and copy it to a new column. I need also to repeat this by taking every 20th line from the orginal column. How could I do this?

I tried to google a solution for this, but couldn't find one that would explain this in detail.

-Sampo Lahtinen

Villeroy · Posted: Mon Oct 10, 2011 8:58 am Post subject:

=MOD(ROW();20)
copy down
filter by zero
copy filtered range
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

lampo · Newbie Joined: 10 Oct 2011 Posts: 4

Possible to explain in more detail? Because nothing wanted happens.

Villeroy · Posted: Mon Oct 10, 2011 10:20 am Post subject:

A forum can't teach you how to do anything in a spreadsheet.
http://wiki.services.openoffice.org/wiki/Documentation/OOo3_User_Guides/Calc_Guide
(or any used Excel book from the flea market)
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

lampo · Newbie Joined: 10 Oct 2011 Posts: 4

yes of course I understand that, and i know what that MOD operator does and so on, i just dont know how to apply it to this problem.

Villeroy · Posted: Mon Oct 10, 2011 10:59 am Post subject:

Your list in A1:Y999
Z1 =MOD(ROW();20)
Copy down Z1 until Z999
Data>Filter>Standard... (or auto filter)
Column Z = 0 (or any other row index between 0 and 20 you want to copy)
Copy the filtered list.
Paste on another sheet.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

lampo · Newbie Joined: 10 Oct 2011 Posts: 4

thank you =) got it working!

lrohr · Newbie Joined: 31 Jan 2012 Posts: 1

If you want to automate it, there is another way :

=INDIRECT(ADDRESS(((Row()-1)*5;4))

just put it in the cells you want the values to copy to

I explain it step by step :

ADDRESS returns a address as text and takes 2 arguments:
1. the number of the row
2. the number of the column

So for the first argument I took the Row() function which gives me the number of the row I'm in. So from this number I subtracted 1 because I started in the 2. row ... this value I multiplied by 5 so for the first cell I copied it to I get a :
"$D$5" for the next cell below that one I get a
"$D$10" below that a
"$D$15" and so on.

The INDIRECT function then returns the contents of the reference text form.

Note that you can of course also use COLUM()
and with this function you can switch the rows to colums and vice versa ...

greetings lrohr

AMTEK · Newbie Joined: 19 Sep 2012 Posts: 4

I am trying to do something similar as the OP. I have spent hours attempting to follow the two options detailed here and can't manage it.

Here is an example of what I am trying to accomplish..

A1: Company Name
A2: PHONE
A3: FAX
A4: BLANK CELL
A5: Company Name
A6: PHONE
A7: FAX
A8: BLANK CELL

This sequence continues and I need the information in a different layout. What I need is:

E1: Company Name F1: PHONE G1: FAX
E2: Company Name F2: PHONE G2: FAX
E3: Company Name F3: PHONE G3: FAX

The blank cells are gone and the information for each company is now listed side by side.

Using the MOD function detailed above I am able to assign numerical values to the cells and filter the BLANK CELLS out.

Using the INDIRECT and ADDRESS function detailed above I am able to duplicate the contents of A1:A3 into E1, F1 and G1.

What I can't do is combine the 2 functions or copy the INDIRECT/ADDRESS function down to replicate what I have in E1, F1, G1 with the data in A5:A8 and so forth throughout the spreadsheet.

Anybody have a solution?

ozzie · Posted: Wed Sep 19, 2012 2:18 pm Post subject:

In E1 dragged across to H1and down asfar as neccessary
(note this is for a four row cycle as per your example - including blank row,
and this is for a 100 row column 'A' - adjust as neccessary)

Robert Tucker · Posted: Wed Sep 19, 2012 2:25 pm Post subject:

E column: =INDIRECT((ADDRESS((ROW()-1)*4+1;1)))
F column: =INDIRECT((ADDRESS((ROW()-1)*4+2;1)))
G column: =INDIRECT((ADDRESS((ROW()-1)*4+3;1)))
_________________
LibreOffice 3.6.6 on Fedora 18, LibreOffice 4.0.2 on Ubuntu 13.04 (Double Boot)

AMTEK · Newbie Joined: 19 Sep 2012 Posts: 4

Worked. Thank you!