Wednesday, July 18, 2012

How to Skip Every nth Row in Excel

I recently acquired data which had a temporal resolution that was much too fine than necessary. Imagine plotting a graph with 121,957 data points! Horrendous! It slows down everything in Excel.
Data came from eight RH and Temp sensors that have been logging a little under every second over a span of two days.

I don't need to know that temp every second, maybe every minute or every 5 minutes should be ok. So I searched the net for solutions and found this:

http://www.lytebyte.com/2009/06/02/how-to-select-every-nth-row-in-excel/

First time to use the mod and data filter function and they work like a charm!

In 3 easy steps here is the trick:

  1. On a blank column beside the data type =mod(A(1), 60). Copy and drag all the way down to the last data you'd like to include. Replace 60 by any row number you'd like to skip. This will generate a column  which cycles from 0 to 60.
  2. Use Data/Filter on the modulo column and select the number you'd like to remain. Say you choose 25. Every row labeled 25 in the modulo column will remain and the rest will be hidden.
  3. Et voila! Select remaining data rows and plot away!




No comments: