Removing Data from a worksheet [Closed]

Report
-
 Pram -
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798 > Pram
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.
>
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015

oh ok, sorry i got confused. I will try that again! thx :)
>
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015

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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
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
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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798 > PRam
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.
>
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015

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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
798
IMPORTANT NOTE

before invoking test1 with the ORIGINAL DATA SAVE IT SOMEWHERE SAFELY (though I have made provision of keeping a copy in sheet4).
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
Posts
1
Registration date
Friday February 5, 2010
Status
Member
Last seen
February 8, 2010

yes it is wiser to backup once more even if there is a copy in sheet4

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!