Removing Data from a worksheet

Closed
PRam - Feb 4, 2010 at 11:35 PM
 Pram - Feb 8, 2010 at 09:49 PM
Hello,
I am wondering if anyone has a code for this problem. It may be simple for someone who knows VB well.

I have large datasets collected over several years in excel. My task is to identify and remove (may be move to another worksheet) those months that have (a) 3 consecutive days of data missing and or (b) a total of 5 days of data missing.

I could do this manually but i think it is possible to write a code for this!? Each work sheet has about 30000 rows of entries. with about 6 columns.

Any suggestions?
Thanks in adavance

5 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 5, 2010 at 10:44 PM
It is not clear. do you want to cut and paste instead of copy and paste.
if you cut and paste the dates rows with gaps of 3 or 5 days will be removed from the main sheet. You need not worry for doing this operation month by month do it one stroke.
remember if you just remove you mess up with data and some data which satisfied the above condition will be lost. is it ok with you. on this assumption let me first give you experimental data
EXPERIMENTAL DATA
date data data data
18-Feb-06 35 31 46
22-Sep-06 78 36 66
18-Nov-06 28 60 69
19-Nov-06 69 14 44
11-Oct-07 83 45 19
25-Dec-07 55 94 19
14-Jul-08 18 12 94
16-Jul-08 44 70 98
6-Jan-09 95 92 16
12-Jan-09 1 2 3
14-Jan-09 4 5 6

OPEN A NEW WORKSHEET CALL IT "SHEET4"

run the macro test1 ON THIS EXPERIMENTAL DATA and see what happens is this what you want
GIVE FEEDBACK.
Sub undo()
Worksheets("sheet1").Cells.Clear
Worksheets("sheet4").UsedRange.Copy Worksheets("sheet1").Range("A1")
End Sub


Sub test1()
Dim j As Integer, k As Integer
Worksheets("sheet4").Cells.Clear
Worksheets("sheet1").UsedRange.Copy
Worksheets("sheet4").Range("A1").PasteSpecial
With Worksheets("sheet1")
j = .Range("A1").End(xlDown).Row
For k = j To 3 Step -1
If .Cells(k, 1) - .Cells(k - 1, 1) >= 3 And _
    .Cells(k, 1) - .Cells(k - 1, 1) >= 5 Then
  Range(.Cells(k, 1), .Cells(k - 1, 1)).EntireRow.Delete
  End If
  Next
  End With
  Worksheets("sheet1").Activate
End Sub


1
hi I tried the data and the code. The data gets copied to sheet 4 whether or not there is a gap in the series. I tried running the same on a part of my data, with known gaps and it didint distingusih whether there is any gap in the dates or not. what do you think?
thanks again
preeti
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810 > Pram
Feb 8, 2010 at 07:44 PM
there is some confusion. copying of the original data in sheet 4 is not immediately relevant but it is only to preserve the original data.
the operative part is removing the data where there is a gap and this is done in sheet1. after running the macro scrutinise sheet 1 repeat sheet1 and post feedback.
0
Pram > venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021
Feb 8, 2010 at 09:21 PM
oh ok, sorry i got confused. I will try that again! thx :)
0
Pram > venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021
Feb 8, 2010 at 09:31 PM
hi, ok, it worked (I think) but I need to just make sure. This is the data set I used in sheet 1. Then Ran the TEST1 macro on it. Sheet 4 was an exact copy of sheet 1. Then when i checked sheet 1 again, only 2 lines of data were left (for the 27 and 28th). does that make sense? I'm assuming that the other entries were eliminated beacuse of gaps? My question is, why did excel remove the entries from 2/1/1915-2/181915. I hadnt create the gaps in the data until after those entries. Would it make sense if that set was retained in sheet 1 as well?


date data data data date data data data date data data data date data data data
2/1/1915 1915 2 1 -5 -15.6 -10.3
2/2/1915 1915 2 2 -2.8 -13.3 -8.1
2/3/1915 1915 2 3 -2.8 -14.4 -8.6
2/4/1915 1915 2 4 -5.6 -15 -10.3
2/5/1915 1915 2 5 -1.7 -22.8 -12.3
2/6/1915 1915 2 6 -5 -20.6 -12.8
2/7/1915 1915 2 7 -1.1 -17.8 -9.5
2/8/1915 1915 2 8 0 -15 -7.5
2/9/1915 1915 2 9 1.1 -13.9 -6.4
2/18/1915 1915 2 18 3.3 -8.9 -2.8
2/19/1915 1915 2 19 -2.2 -22.2 -12.2
2/20/1915 1915 2 20 -1.1 -12.8 -7
2/21/1915 1915 2 21 -1.7 -23.9 -12.8
2/22/1915 1915 2 22 -1.7 -22.8 -12.3
2/23/1915 1915 2 23 0.6 -17.2 -8.3
2/24/1915 1915 2 24 -0.6 -20.6 -10.6
2/25/1915 1915 2 25 0.6 -18.3 -8.9
2/26/1915 1915 2 26 -2.8 -12.2 -7.5
2/27/1915 1915 2 27 2.2 -15 -6.4
2/28/1915 1915 2 28 0 -17.8 -8.9


MACRO RUN

SHEET 4 date data data data date data data data date data data data date data data data
2/1/1915 1915 2 1 -5 -15.6 -10.3
2/2/1915 1915 2 2 -2.8 -13.3 -8.1
2/3/1915 1915 2 3 -2.8 -14.4 -8.6
2/4/1915 1915 2 4 -5.6 -15 -10.3
2/5/1915 1915 2 5 -1.7 -22.8 -12.3
2/6/1915 1915 2 6 -5 -20.6 -12.8
2/7/1915 1915 2 7 -1.1 -17.8 -9.5
2/8/1915 1915 2 8 0 -15 -7.5
2/9/1915 1915 2 9 1.1 -13.9 -6.4
2/18/1915 1915 2 18 3.3 -8.9 -2.8
2/19/1915 1915 2 19 -2.2 -22.2 -12.2
2/20/1915 1915 2 20 -1.1 -12.8 -7
2/21/1915 1915 2 21 -1.7 -23.9 -12.8
2/22/1915 1915 2 22 -1.7 -22.8 -12.3
2/23/1915 1915 2 23 0.6 -17.2 -8.3
2/24/1915 1915 2 24 -0.6 -20.6 -10.6
2/25/1915 1915 2 25 0.6 -18.3 -8.9
2/26/1915 1915 2 26 -2.8 -12.2 -7.5
2/27/1915 1915 2 27 2.2 -15 -6.4
2/28/1915 1915 2 28 0 -17.8 -8.9


DATA LEFT ON SHEET 1
date data data data date data data data date data data data date data data data
2/27/1915 1915 2 27 2.2 -15 -6.4
2/28/1915 1915 2 28 0 -17.8 -8.9
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Feb 5, 2010 at 05:16 AM
You need to share more info about the layout of the sheet or may be post the workbook at some site like https://authentification.site or any other place
0
hi thanks for reviewing my question. this is what the dataset would look like:
Date/Time Year Month Day Data Quality Max Temp (°C) Max Temp Flag Min Temp (°C)
1/1/1922 1922 1 1 -7.2 -21.1
1/2/1922 1922 1 2 -6.7 -18.3
1/3/1922 1922 1 3 -16.1 -32.2
1/4/1922 1922 1 4 -15.6 -34.4
1/5/1922 1922 1 5 -11.7 -32.2
1/6/1922 1922 1 6 -9.4 -25.6
1/7/1922 1922 1 7 -9.4 -24.4
1/8/1922 1922 1 8 -1.1 -11.7
1/9/1922 1922 1 9 -1.7 -5
1/10/1922 1922 1 10 -7.8 -18.9
1/11/1922 1922 1 11 -2.2 -24.4
1/12/1922 1922 1 12 -1.7 -17.2

I would need the macro to remove and save on a seperate sheet those months that have gaps in them in terms of 3 consecutive missing dates or 5 dates in any given month. I have already sorted and removed those rows that have no data for a given day. So I think at this stage, i would have to get excel to read down a column ("date") and identify whther or not a month is represented for 30/31/or 38/29 days.



thanks again for your help!!
-P
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 5, 2010 at 05:41 AM
you have not given complete information
in which column dates are entered
if between two dates in that column which rows are to be copied. for example dates in row 2 and row 3 are more than 3 years old would you like to copy both rows 2 and 3.

my assumption is
dates are in column A in sheet 1
you want both rows to be copied

Keep the original data base safely somewhere so that it can be retrieved if there is a mess up


the sample data is something like this

date
18-Feb-06
22-Sep-06
18-Nov-06
19-Nov-06
11-Oct-07
25-Dec-07
14-Jul-08
16-Jul-08
6-Jan-09
you copy this data in sheet1 of experimental workbook from A1 to A10
they try this macro
if you are satsified use th macro to your original file
REMEMBER THE DATA SHOULD BE SORTED ACCORDING TO DATA COLUMN ASCENDING
THE UNDO MACRO IS CLEAR SHEETS 2 AND 3 SO THAT YOU CAN AGAIN TEST THE MACRP
the macro is
Sub test()
Dim r As Range, c As Range, j As Integer, k As Integer
With Worksheets("sheet1")
Set r = Range(.Range("a2"), .Range("a2").End(xlDown))
For j = 3 To 5 Step 2
k = WorksheetFunction.RoundUp(j / 3, 0) + 1
'MsgBox k
For Each c In r
If c.Offset(1, 0) - c >= k Then
Range(c, c.Offset(1, 0)).EntireRow.Copy
With Worksheets("sheet" & k)
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End If
Next
Next
End With
End Sub



Sub undo()
Dim j As Integer, k As Integer
For j = 3 To 5 Step 2
k = WorksheetFunction.RoundUp(j / 3, 0) + 1
Worksheets("sheet" & k).Cells.Clear
Next
End Sub




If there is change in assumption clearly elucidate them with examples
0
hi this is what the dataset would look like:
Date/Time Year Month Day Data Quality Max Temp (°C) Max Temp Flag Min Temp (°C)
1/1/1922 1922 1 1 -7.2 -21.1
1/2/1922 1922 1 2 -6.7 -18.3
1/3/1922 1922 1 3 -16.1 -32.2
1/4/1922 1922 1 4 -15.6 -34.4
1/5/1922 1922 1 5 -11.7 -32.2
1/6/1922 1922 1 6 -9.4 -25.6
1/7/1922 1922 1 7 -9.4 -24.4
1/8/1922 1922 1 8 -1.1 -11.7
1/9/1922 1922 1 9 -1.7 -5
1/10/1922 1922 1 10 -7.8 -18.9
1/11/1922 1922 1 11 -2.2 -24.4
1/12/1922 1922 1 12 -1.7 -17.2

I would need the macro to remove and save on a seperate sheet those months that have gaps in them in terms of 3 consecutive missing dates or 5 dates in any given month. I have already sorted and removed those rows that have no data in the other columns for a given day. So I think at this stage, i would have to get excel to read down the "date"column and identify whether or not a month is represented for 30/31/or 38/29 days resp.

Does that make sense? will the macro below apply to this?

thanks again for your help!!
-P
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810 > PRam
Feb 5, 2010 at 09:12 AM
the no. of days in the month is not relevant
date arithmetic is versatile

in your experimental data the data are from january 1 1922 to january 12 1922.
there are neight 3 days gap or 5 days gap because all the days are serial.

to find the no of days between two days you have to find the difference between those two daysl
e.g.
A1 2/28/2010 that is 28th feb 2010
A2 3/3/2010 that is 3rd March 2010

suppose in any empty cell type this formula

=A2-A1
and format this cell as number with no decimals. it will give 3
that is there are 3 days gap between these two dates.

I am basing my macro on this. But your experimental data do not have any 3 days or 5 days gap and so nothing will be copied in any other sheet.

I suggest you copy my sample data in a new workbook and run the macro and see sheet 2 and sheet 3 and then give your comments.
0
PRam > venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021
Feb 5, 2010 at 10:28 AM
hi I tested the data you sent and set up a data set similarly. the macro works!! perfectly!! the result was - data with 3 days gap was copied to the seond and 3rd sheets.

Can I amend the code to select the entire month and move it to another sheet if 3 or 5 days are missing?

for example, say in the month of feb, 1999, data is missing for the 5,6,7th. this renders that month useless for the analysis, so I would need to remove this entire month. Similarly, if in March, 1999 , the 2,5, 16, 20, 31, are missing, that month is also rendered invalid.

Is this doable? I ask, as i have about 100 years on avereage for each data set and would rather not do this manually ;)
thanks
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Feb 5, 2010 at 10:48 PM
IMPORTANT NOTE

before invoking test1 with the ORIGINAL DATA SAVE IT SOMEWHERE SAFELY (though I have made provision of keeping a copy in sheet4).
0
Just wanted to clarify what I'm trying to achieve with this marcro.
Criteria to be used on two independent datasets :
1. This is for temperature values A month is discarded if 3 consecutive days or more than a total of 5 days in a month were missing .
2. This is for a different set of values: A month is discarded if even 1 value was missing

thanks
0

Didn't find the answer you are looking for?

Ask a question
jackquil36 Posts 1 Registration date Friday February 5, 2010 Status Member Last seen February 8, 2010
Feb 8, 2010 at 11:32 AM
yes it is wiser to backup once more even if there is a copy in sheet4
0