Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows of Data
I have a spreadsheet with 38000 rows of data. The data comes in with date
(01/01/01) in column A and time (12:00:00) in Column B. The data is recorded every 5 secs. I would only want to keep data every 5 mins and delete the rest. I gather this can be done in two ways: 1) by deleting 'x' number of rows below the first data and repeat the process. 2) by matching values **:00:00, **:05:00, **:10:00 and such in Column B and deleting the rest of the columns. Can anyone help me with a macro to do this? Many thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows of Data
Here's a manual way. Add a helper (temporary) column to your table, with a heading (if your
table doesn't have a heading row, this won't work unless you add one). Put the following formula in the first row below the heading row: =MOD(MINUTE(B2),5)<0 All the rows that aren't 5-minute multiples will say TRUE. Now apply an autofilter to your table (Data - Filter - Autofilter). Filter the helper column for TRUE (Click the helper's dropdown and select TRUE). This should result in all the rows you don't want. Now select all the rows (click the row header of the first, then press Ctrl-Shift-DownArrow). Delete the rows (Edit - Delete, or Ctrl-Minus). Now remove the autofilter (Data - Filter - Autofilter). Delete the helper column. Done. -- Regards from Virginia Beach, Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "SU" wrote in message ... I have a spreadsheet with 38000 rows of data. The data comes in with date (01/01/01) in column A and time (12:00:00) in Column B. The data is recorded every 5 secs. I would only want to keep data every 5 mins and delete the rest. I gather this can be done in two ways: 1) by deleting 'x' number of rows below the first data and repeat the process. 2) by matching values **:00:00, **:05:00, **:10:00 and such in Column B and deleting the rest of the columns. Can anyone help me with a macro to do this? Many thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows of Data
I'm assume the date in column A and time in column B are in Serial time
format. The dates are whole number and the time is less than 1. You can easily verify this by changing the format of column A and B to Number (menu Format - Cells - Number). Date in excel starts at Jan 1, 1900 and adds one for each day. An hour is 1/24 and a minute is 1/((24*60). five minutes is 5/((24*60) . Becuase your data is probably running 24 hours a day you need to consider what happens at midnight. The easiest way of subtracting 5 minutes from your data is to add the days and hours together then subtract 5 minutes which I did below. then remove rows where the time is less than this time. Sub deleteRows() Dim Less5 As Double Dim RowTime As Double Less5 = Now - (5 / (24 * 60)) RowCount = 1 Do RowTime = Range("A" & RowCount).value + Range("B" & RowCount).value If RowTime < Less5 Then Rows(RowCount).Delete End If RowCount = RowCount + 1 Loop While RowTime < Less5 End Sub "SU" wrote: I have a spreadsheet with 38000 rows of data. The data comes in with date (01/01/01) in column A and time (12:00:00) in Column B. The data is recorded every 5 secs. I would only want to keep data every 5 mins and delete the rest. I gather this can be done in two ways: 1) by deleting 'x' number of rows below the first data and repeat the process. 2) by matching values **:00:00, **:05:00, **:10:00 and such in Column B and deleting the rest of the columns. Can anyone help me with a macro to do this? Many thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows of Data
Thank you Earl & Joel. It works.
"SU" wrote: I have a spreadsheet with 38000 rows of data. The data comes in with date (01/01/01) in column A and time (12:00:00) in Column B. The data is recorded every 5 secs. I would only want to keep data every 5 mins and delete the rest. I gather this can be done in two ways: 1) by deleting 'x' number of rows below the first data and repeat the process. 2) by matching values **:00:00, **:05:00, **:10:00 and such in Column B and deleting the rest of the columns. Can anyone help me with a macro to do this? Many thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows with data duplicated in 2 columns | Excel Worksheet Functions | |||
deleting various rows of cell data throughout the master list tha. | Excel Discussion (Misc queries) | |||
Pivot Table - Deleting data rows while maintaining them in the tot | Excel Worksheet Functions | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions | |||
Deleting rows containing common data | Excel Discussion (Misc queries) |