Removing duplicates within time range

[justmiller]

Hello,

I am using Excel 2007 on Windows 7. I would like to remove duplicates as follows: If column A = column A, column B = column B, and the two rows are within one hour of each other, then remove all duplicates, leaving one instance of the row. As you can see, sometimes the duplicates are right next to each other, and sometimes have rows between them. (Rows 1 & 2, Rows 30 & 32). Even though this sample doesn't show it, sometimes there can be tens of duplicates within an hour, not just two.

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Eruption</td><td style=";">I Can't Stand The Rain (78)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">9:12:55 AM</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Eruption</td><td style=";">I Can't Stand The Rain (78)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">9:10:02 AM</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Chicago</td><td style=";">Just You 'N' Me (73)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">9:05:59 AM</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">The Main Ingredient</td><td style=";">Everybody Plays The Fool (72)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">9:03:28 AM</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">The Main Ingredient</td><td style=";">Everybody Plays The Fool (72)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">9:02:49 AM</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Foreigner</td><td style=";">Hot Blooded (78)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:58:44 AM</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Cymarron</td><td style=";">Rings (71)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:56:33 AM</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">Bee Gees</td><td style=";">Nights On Broadway (75)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:51:43 AM</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">Carpenters</td><td style=";">Superstar (71)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:48:02 AM</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">Badfinger</td><td style=";">Day After Day (72)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:45:04 AM</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">James Brown</td><td style=";">Get On The Good Foot - Pt 1(72)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:41:53 AM</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">Eagles</td><td style=";">One Of These Nights (75)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:37:26 AM</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">Donna Summer</td><td style=";">Dim All The Lights (79)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:33:26 AM</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">Electric Light Orchestra</td><td style=";">Telephone Line (77)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:29:24 AM</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style=";">Earth, Wind + Fire</td><td style=";">Sing A Song (76)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:25:45 AM</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style=";">Bonnie Tyler</td><td style=";">It's A Heartache (78)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:22:17 AM</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style=";">Santana</td><td style=";">Black Magic Woman...(70)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:17:33 AM</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style=";">B.T. Express</td><td style=";">Do It ('Til You're Satisfied) (74)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:14:38 AM</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">Toto</td><td style=";">Hold The Line (79)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:10:45 AM</td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style=";">White Plains</td><td style=";">My Baby Loves Lovin' (70)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:08:02 AM</td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style=";">Lynyrd Skynyrd</td><td style=";">Sweet Home Alabama (74)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:04:23 AM</td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style=";">ABBA</td><td style=";">Knowing Me, Knowing You (77)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">8:04:03 AM</td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style=";">ABBA</td><td style=";">Knowing Me, Knowing You (77)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:59:58 AM</td></tr><tr><td style="color: #161120;text-align: center;">24</td><td style=";">Bachman-Turner Overdrive</td><td style=";">Let It Ride (74)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:56:37 AM</td></tr><tr><td style="color: #161120;text-align: center;">25</td><td style=";">Diana Ross</td><td style=";">Ain't No Mountain High Enough (70)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:53:03 AM</td></tr><tr><td style="color: #161120;text-align: center;">26</td><td style=";">Paul McCartney & Wings</td><td style=";">Junior's Farm (74)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:48:54 AM</td></tr><tr><td style="color: #161120;text-align: center;">27</td><td style=";">Freda Payne</td><td style=";">Band Of Gold (70)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:45:41 AM</td></tr><tr><td style="color: #161120;text-align: center;">28</td><td style=";">Daryl Hall & John Oates</td><td style=";">She's Gone (76)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:42:24 AM</td></tr><tr><td style="color: #161120;text-align: center;">29</td><td style=";">Raspberries</td><td style=";">Go All The Way (72)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:39:23 AM</td></tr><tr><td style="color: #161120;text-align: center;">30</td><td style=";">Leif Garrett</td><td style=";">I Was Made For Dancin' (78)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:39:02 AM</td></tr><tr><td style="color: #161120;text-align: center;">31</td><td style=";">Raspberries</td><td style=";">Go All The Way (72)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:38:42 AM</td></tr><tr><td style="color: #161120;text-align: center;">32</td><td style=";">Leif Garrett</td><td style=";">I Was Made For Dancin' (78)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:35:23 AM</td></tr><tr><td style="color: #161120;text-align: center;">33</td><td style=";">Jigsaw</td><td style=";">Sky High (75)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:32:54 AM</td></tr><tr><td style="color: #161120;text-align: center;">34</td><td style=";">Cornelius Bros. & Sister Rose</td><td style=";">Treat Her Like A Lady (71)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:30:13 AM</td></tr><tr><td style="color: #161120;text-align: center;">35</td><td style=";">Neil Diamond</td><td style=";">Song Sung Blue (72)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:27:25 AM</td></tr><tr><td style="color: #161120;text-align: center;">36</td><td style=";">Hamilton, Joe Frank and Reynolds</td><td style=";">Don't Pull Your Love (71)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:24:26 AM</td></tr><tr><td style="color: #161120;text-align: center;">37</td><td style=";">Mungo Jerry</td><td style=";">In The Summertime (70)</td><td style="text-align: right;;">3/7/2012</td><td style="text-align: right;;">7:21:28 AM</td></tr></tbody></table>

Sheet1​

Anyone have any suggestions how to go about this? Thank you in advance.

 

[JoeMo]

Try this on a copy of your sheet (untested). Assumes your data start in A1 and are as your posted example.

Sub RemoveDupsWithinOneHour()
Dim lRw As Long, R As Range, zA(), zB(), zC(), zD(), zE(), zF()

lRw = Range("A" & Rows.Count).End(xlUp).Row
Set R = Range("A1", "D" & lRw)
ReDim zA(1 To R.Rows.Count)
ReDim zB(1 To R.Rows.Count)
ReDim zC(1 To R.Rows.Count)
ReDim zD(1 To R.Rows.Count)
ReDim zE(1 To R.Rows.Count)
ReDim zF(1 To R.Rows.Count)
Application.ScreenUpdating = False
For i = 1 To R.Rows.Count
    zA(i) = R.Cells(i, 1).Value
    zB(i) = R.Cells(i, 2).Value
    zC(i) = zA(i) & zB(i)
    zD(i) = R.Cells(i, 3).Value
    zE(i) = R.Cells(i, 4).Value
    zF(i) = zD(i) + zE(i)
Next i
R.Columns(3).Insert shift:=xlToRight
R.Columns(3).Value = WorksheetFunction.Transpose(zC)
R.Columns(4).Insert shift:=xlToRight
R.Columns(4).Value = WorksheetFunction.Transpose(zF)
For i = R.Rows.Count To 2 Step -1
    If WorksheetFunction.CountIf(R.Columns(3), R.Cells(i, 3)) > 1 Then
        For j = i - 1 To 1 Step -1
            If R.Cells(i, 3) = R.Cells(j, 3) Then
                If Abs(R.Cells(i, 4) - R.Cells(j, 4)) < 1 / 24 Then
                    R.Rows(i).Delete shift:=xlUp
                End If
            End If
        Next j
    End If
Next i
R.Columns("C:D").Delete
End Sub

 

[justmiller]

This looks to get the job done. Thank you very much for your assistance.