Related:
- Removing Data from a worksheet
- Transfer data from one excel worksheet to another automatically - Guide
- Tmobile data check - Guide
- How do you add a username or author to a worksheet - Guide
- How to remove a number from blacklist - Guide
- Automatically run a macro when opening a worksheet - Guide
5 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Feb 5, 2010 at 10:44 PM
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.
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 5, 2010 at 05:16 AM
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
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Feb 5, 2010 at 05:41 AM
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
If there is change in assumption clearly elucidate them with examples
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
>
PRam
Feb 5, 2010 at 09:12 AM
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.
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.
PRam
>
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
Feb 5, 2010 at 10:28 AM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Feb 5, 2010 at 10:48 PM
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).
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
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
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
Feb 8, 2010 at 11:32 AM
yes it is wiser to backup once more even if there is a copy in sheet4
Feb 8, 2010 at 10:55 AM
thanks again
preeti
Feb 8, 2010 at 07:44 PM
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.
Feb 8, 2010 at 09:21 PM
Feb 8, 2010 at 09:31 PM
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