How to collect data from every 60th row?

[Guest]

Have a spreadsheet with monthly weather data on different sheets. There are
about 48,000 rows (the data is collected automatically every minute).
What sort of formula could you have to copy say one line for every hour
(i.e. every 60th row) and than paste that data into a new row on a separate
sheet?!
Many thanks

 

[Peo Sjoblom]

Use a help column, assume your data starts in A2, in B2 put

=MOD(ROWS($A$1:A1),60)=1

this will include the first cell A2 and next would be A61, if you want to
start with A60 as the first
change the 1 to 0 at the end

apply autofilter and filter on TRUE

copy visible cells in A and paste them wherever you want

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Biff]

Hi!

One way:

Assume data is in the range Sheet1 A1:An.

On another sheet enter this formula and copy down as needed:

=INDEX(Sheet1!A:A,(ROWS($A$1:$A1)-1)*60)

It'll return values from:

Sheet1 A1
Sheet1 A60
Sheet1 A120
Sheet1 A180
Sheet1 A240
Sheet1 A300
etc

Biff