Is there a way to choose every 4th or 12th line in an Excel spreadsheet? I have about a year’s worth of temperature data taken at 5 and 15 minute intervals, but need to coalesce it down to 1 hour intervals to make the dataset more manageable. I can import this into Access if necessary, but I’m hoping there’s a command I can use. The data is in columns:
Date / Time / Temp F1 / Temp C1 / Temp F2 / Temp C2 / Temp F3 / Temp C3
I can use the find function for the Time column with wildcards for the hours, to find one line of data for each hour, but I haven’t figured out a way to sort to find, say all rows with time **:00:00.
If anyone can help out, I’ll be eternally grateful.
Near as I can figure out, importing to Access is your best bet. Databases are far more flexible at filtering data. If you need help with the accesss side of this e-mail me; my address should be in my profile.
1> Put a Serial Number in the first column to the readings you have.
2> Insert a new sheet - In the new sheet number the first column with the corresponding readings you want from the first sheet : like
1
13
26
39
…
3> Use the finction Vlookup Example - VLOOKUP(Sheet1!A1,Sheet1!A1:F14,2). Like in this example VLOOKUP will find the corresponding row from Sheet1 to the value in the leftmost cell.
The problem with filtering time data in Excel is that although it’s displayed (assuming it’s formatted as “time”) in, say, xx:yy:zz format, it’s stored in Excel as a decimal number. Thus, 01:00:00 is stored as 0.000694 (1/1440, or 1/(24*60)).
The easiest way I can figure to get what you want–asssuming andy_fl’s method won’t work because you don’t want to parse each serial number in the list and make the comparison manually–would be to add another column to the data. Assuming your first time value is B2, and you’ve inserted a column at C, the formula in C2 would be “=B21440-TRUNC(B21440)”. Drag that formula down to populate the entire column. This places either a 1.000 or 0.000 (depending on format) in the cells with an even hourly time. Then you can use the custom autofilter to select only those values that “equal 1.0000” OR “equal 0.0000”.
Oh, yes–I forgot to mention that zoid’s solution is the more elegant. But not eveyone’s familiar with Access, or in the mood to make the move from spreadsheet to RDB.
this simple macro will throw out a group of rows between rows it keeps
i have inlcuded notes for changing variables to modify the behavior
TEST ON COPY of file first.
Just cut and paste the following into the macro editor in excel
Sub delete()
’
’ testingdelete Macro
’ Macro recorded 12/14/2002 by billy
’
’
Dim i As Variant
Dim ii As String
Dim j As Variant
Dim jj As String
Dim c As Variant
Dim d As Variant
c = 100 ’ this number is the total length of the file you expect
'when done throwing out data, not the number you start with.
d = 2 'this number + 1 is the number of lines of data thrown out between points
For i = 1 To c
j = i + d
jj = j
ii = i
Range(“a” + ii + “:a” + jj).Select
Selection.EntireRow.delete
Next i
End Sub
I tested the above macro as it is written and if the left column satrts with 1,2,3,4,5,6,7,8, it will read 4,8,16,20,24 when finished
every 4th line saved, all others are deleted
I’ve got one set all the way down to a mere 7,000 rows.
I did figure out how to import into Access and do a simple filter that works pretty well. The problem with some of the queries and macros is that some data sets were recorded at :00 seconds, some at :45 seconds, etc. Also, some were on 5 minute intervals, some on 10, some on 15, and I’m not familiar enough with the syntax to make them work easily. I think I’ll just trudge through filtering in Access then moving it back into Excel.
