How do I pull out every nth value from a column of data in excel?

[Guest]

I have two sets of data: one which has information every hour, and another
with information every 15 min. I want to down-size the data that has infm
every 15 min so that I can look at it on the hourly scale and compare to the
other data set. The data set is too large to manually delete the extra rows.
I'm using Excel 2003.

 

[RagDyer]

Say your 15 minute values are in Column B, starting in B1.

You would want to return:
B1 - B5 - B9 - ...etc.

Enter this formula along side your hourly values, and copy down as needed:

=INDEX(B:B,4*ROWS($1:1)-4+1)

 

[Kypp]

I have two sets of data: one which has information every hour, and another
with information every 15 min. I want to down-size the data that has infm
every 15 min so that I can look at it on the hourly scale and compare to the
other data set. The data set is too large to manually delete the extra rows.
I'm using Excel 2003.

You could code for a VBA program on button that collates the lines you
want

If column A is the column with the 15 min values in it and column B has
the hourly values in it try this VBA code in a button. Save a new copy
of the data before editing anything.

a = 0
b = 0

Do Until a = X (depends on the size of your data range)
Range("C1").offset(b, 0) = Range("A1").offset(a, 0)
a = a + 4
b = b + 1
Loop

This should copy every 4th line in column A to column C without any row
spaces. X will be the difference between the top row of data and the
bottom row of data in your 15 min column.

Hope this helps.

 

[Dav]

with out seeing your data the easiest way is probably to create a helpe
column eg calcuating the minutes of the times perhaps minute(b1), o
using a left right or mid function if the time is text eg right(b1,2)

Then copy the formula down to the bottom of your data, select you
data, then sort by this column and then delete the rows you do not wan
as a block, rather than individually.

Regards

Da

 

[MartinW]

Hi m.mines,

One more way. Insert a new column, put the numbers 1to 4 in
the first 4 cells then click and drag the fill handle down to the
end of your data hold down control and release your left button.
Then use autofilter to select every fourth row.

HTH
Martin

 

[Guest]

Thank you!
M.

 

[Guest]

Thanks!

 

[Guest]

Thank you, I thought there might be a way to do it that way.

 

[Guest]

Thank you. That makes sense.