Deleting duplicate Date/time data (excel 2007)

[mzalikhan]

Hi everybody

I have hourly meteorological data for 10 years. The problem is that some of the data entries are repeated. e.g in column A, i have

11-Aug-2003 15:00
11-Aug-2003 16:00
11-Aug-2003 16:00
11-Aug-2003 17:00
11-Aug-2003 18:00

What i need to have is "no repetition of any of the data entry" i.e.

11-Aug-2003 15:00
11-Aug-2003 16:00
11-Aug-2003 17:00
11-Aug-2003 18:00


Please keep in mind that for some years, the data entries are at Half Past hours e.g.

18 April 2007 13:30
18 April 2007 14:30
18 April 2007 14:30
18 April 2007 15:30
and so on.

Peace
Muhammad Zeeshan

 

[alansidman]

You may want to try using this code:

Sub DeleteDuplicateRows()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DeleteDuplicateRows
' This will delete duplicate records, based on the Active Column. That is,
' if the same value is found more than once in the Active Column, all but
' the first (lowest row number) will be deleted.
'
' To run the macro, select the entire column you wish to scan for
' duplicates, and run this procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim r As Long
Dim n As Long
Dim V As Variant
Dim rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(rng.Row, "#,##0")
n = 0
For r = rng.Rows.Count To 2 Step -1
If r Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(r, "#,##0")
End If
V = rng.Cells(r, 1).Value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Note that COUNTIF works oddly with a Variant that is equal to vbNullString.
' Rather than pass in the variant, you need to pass in vbNullString explicitly.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(rng.Columns(1), vbNullString) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(rng.Columns(1), V) > 1 Then
        rng.Rows(r).EntireRow.Delete
        n = n + 1
    End If
End If
Next r
EndMacro:
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(n)

End Sub

 

[mzalikhan]

That worked

Thanks Alan.

Peace
Muhammad Zeeshan


P.S If possible, please see this one also.

http://www.mrexcel.com/forum/showthread.php?t=553737