(VBA) How can I remove duplicates within an hour of each other (not just the same hour)?

M

moddddmyexcel

New Member
Joined
Mar 6, 2023
Messages
23
Office Version
  1. 365
Platform
  1. Windows
I want to remove duplicates on Time, User ID, and To Location. My data set for time though comes down to the second. I want to remove duplicates that occur within the same hour even if the hour flips over. A scan at 8:30 and another at 9:10 recognized as duplicates. I'm not sure how to go about writing the formula into a helper column or adding to the VBA (VBA Preferred).

VBA Code: 
Sub Remove()
On Error GoTo Error
    Application.ScreenUpdating = False
    Sheets("Data").Unprotect Password:=""
    With Sheets("Data")
    With .Range("A2", .Range("F" & Rows.Count).End(xlUp))
      .Columns(5).Value = .Worksheet.Evaluate(Replace("if(mid(#,2,3)=""TRN"",#&""||""&ROW(#),#)", "#", .Columns(5).Address))
      .Columns(5).Value = .Worksheet.Evaluate(Replace("if(mid(#,2,3)=""EVN"",#&""||""&ROW(#),#)", "#", .Columns(5).Address))
      .RemoveDuplicates Columns:=Array(2, 5), Header:=xlNo
      .Columns(5).Replace What:="||*", Replacement:="", LookAt:=xlPart

''''below is where I want to do the within an hour check on column 1

      .RemoveDuplicates Columns:=Array(1, 2, 5), Header:=xlNo
    End With
    End With
    ActiveWorkbook.Worksheets("Data").Activate
    Application.ScreenUpdating = True
    MsgBox "Duplicates Removed."
    Exit Sub
   
Error:
    Application.ScreenUpdating = True
    MsgBox "Something went wrong; clear, paste, and try again."
End Sub
Book2
ABCDEF
1Date/TimeUser IDLoad NumberFrom LocationTo LocationQuanity
23/8/2023 6:16user123L00066470477LSAET1102test1102
33/8/2023 6:16user124L00066470478LSAET1102test281
43/8/2023 6:17user125L00066470480LSAET1102test3382
53/8/2023 6:17user126GCO0000293872LSAET1102test41344
63/8/2023 6:18user127L00066470481LSAET1102test558
73/8/2023 6:19user1281.01959E+17LSAET1102test61000
83/8/2023 7:08user129L00066470579LSAET1102test7114
93/8/2023 7:08user130L00066470581LSAET1102test8118
103/8/2023 7:08user131GCO0000298623LSAET1102test990
113/8/2023 7:09user132L00066470585LSAET1102test1054
123/8/2023 7:10user133L00066470586LSAET11021VNA23121
133/8/2023 7:11user134L00066470587LSAET1102test1273
143/8/2023 8:45user135GCO0000293437LSAET11022VNA341632
153/8/2023 8:45user133L00066470752LSAET11021VNA23130
163/8/2023 8:46user133L00066470753LSAET11021VNA2376
173/8/2023 8:47user1331.01959E+17LSAET11021VNA231000
183/8/2023 9:17user135L00066470836LSAET11022VNA3451
193/8/2023 9:17user140L00066470845LSAET1102test1855
203/8/2023 9:18user141L00066470858LSAET1102test1967
213/8/2023 9:18user142L00066470889LSAET1102test20167
223/8/2023 9:26user143L00066471063LSAET1102test2156
233/8/2023 9:27user144L00066471076LSAET1102test225
Sheet1
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
What if 3 'duplicates' occurred at 8:30, 9:10 and 10:00?
The first 2 are within an hour of each other, the last 2 are within an hour of each other but first and last are not within an hour of each other.
This scenario happens with your user133 so are your yellow rows indicating that they all do get deleted even though fist & last are not within an hour?
 
Upvote 0
Peter_SSs said:
What if 3 'duplicates' occurred at 8:30, 9:10 and 10:00?
The first 2 are within an hour of each other, the last 2 are within an hour of each other but first and last are not within an hour of each other.
This scenario happens with your user133 so are your yellow rows indicating that they all do get deleted even though fist & last are not within an hour?
Click to expand...
3 duplicates spaced as described I would still like consolidated to 1, my data set should not ever span more than that. This rarely happens, I'm mainly trying to avoid counting something moved early morning and later in the afternoon as one.

I noticed user133 after the fact please ignore the first highlighted one.
 
Upvote 0
moddddmyexcel said:
I'm not sure how to go about writing the formula into a helper column
Click to expand...
Let's start there. Here is something to consider as a possible starting point if Remove duplicates was then just used on col G.

moddddmyexcel.xlsm
ABEG
1Date/TimeUser IDTo Location
28/3/2023 6:16user123test1 
38/3/2023 6:16user124test2 
48/3/2023 6:17user125test3 
58/3/2023 6:17user126test4 
68/3/2023 6:18user127test5 
78/3/2023 6:19user128test6 
88/3/2023 7:08user129test7 
98/3/2023 7:08user130test8 
108/3/2023 7:08user131test9 
118/3/2023 7:09user132test10 
128/3/2023 7:10user1331VNA23 
138/3/2023 7:11user134test12 
148/3/2023 8:45user1352VNA34user135
158/3/2023 8:45user1331VNA23user133
168/3/2023 8:46user1331VNA23user133
178/3/2023 8:47user1331VNA23user133
188/3/2023 9:17user1352VNA34user135
198/3/2023 9:17user140test18 
208/3/2023 9:18user141test19 
218/3/2023 9:18user142test20 
228/3/2023 9:26user143test21 
238/3/2023 9:27user144test22 
Sheet2 (2)
Cell Formulas
RangeFormula
G2:G23G2=IF(COUNTIFS(B$2:B$23,B2,E$2:E$23,E2,A$2:A$23,">="&A2-1/24,A$2:A$23,"<="&A2+1/24)>1,B2,"")



However that would not achieve your desired 'remove duplicates' outcome if it was possible that the same user ID could also have a separate 'within an hour' duplicate group like this. Is this scenario possible with your data?

moddddmyexcel.xlsm
ABEG
1Date/TimeUser IDTo Location
28/3/2023 6:16user123test1 
38/3/2023 6:16user124test2 
48/3/2023 6:17user125test3 
58/3/2023 6:17user135AAAuser135
68/3/2023 6:18user135AAAuser135
78/3/2023 6:19user128test6 
88/3/2023 7:08user129test7 
98/3/2023 7:08user130test8 
108/3/2023 7:08user131test9 
118/3/2023 7:09user132test10 
128/3/2023 7:10user1331VNA23 
138/3/2023 7:11user134test12 
148/3/2023 8:45user1352VNA34user135
158/3/2023 8:45user1331VNA23user133
168/3/2023 8:46user1331VNA23user133
178/3/2023 8:47user1331VNA23user133
188/3/2023 9:17user1352VNA34user135
198/3/2023 9:17user140test18 
Sheet2 (2)
Cell Formulas
RangeFormula
G2:G19G2=IF(COUNTIFS(B$2:B$23,B2,E$2:E$23,E2,A$2:A$23,">="&A2-1/24,A$2:A$23,"<="&A2+1/24)>1,B2,"")
 
Upvote 0
Peter_SSs said:
Sheet2 (2)
Date/Time
User ID​
To Location​
28/3/2023 6:16
user123​
test1​
​
38/3/2023 6:16
user124​
test2​
​
48/3/2023 6:17
user125​
test3​
​
58/3/2023 6:17
user135​
AAA​
user135​
68/3/2023 6:18
user135​
AAA​
user135​
78/3/2023 6:19
user128​
test6​
​
88/3/2023 7:08
user129​
test7​
​
98/3/2023 7:08
user130​
test8​
​
108/3/2023 7:08
user131​
test9​
​
118/3/2023 7:09
user132​
test10​
​
128/3/2023 7:10
user133​
1VNA23​
​
138/3/2023 7:11
user134​
test12​
​
148/3/2023 8:45
user135​
2VNA34​
user135​
158/3/2023 8:45
user133​
1VNA23​
user133​
168/3/2023 8:46
user133​
1VNA23​
user133​
178/3/2023 8:47
user133​
1VNA23​
user133​
188/3/2023 9:17
user135​
2VNA34​
user135​
198/3/2023 9:17
user140​
test18​
​
Click to expand...
This is most definitely a scenario. The "To Location" may even be the same.
 
Upvote 0
moddddmyexcel said:
This is most definitely a scenario. The "To Location" may even be the same.
Click to expand...
Hmm, the "To Location" repeat was looming as my next question.

This seems to be one way and then use column G only as the remove duplicates criterion.

moddddmyexcel.xlsm
ABEG
1Date/TimeUser IDTo LocationHelper
28/3/2023 6:16user123test1user123|test1|8/3/2023 6:16
38/3/2023 6:16user124test2user124|test2|8/3/2023 6:16
48/3/2023 6:17user125test3user125|test3|8/3/2023 6:17
58/3/2023 6:17user135AAAuser135|AAA|8/3/2023 6:17
68/3/2023 6:18user135AAAuser135|AAA|8/3/2023 6:17
78/3/2023 6:19user128test6user128|test6|8/3/2023 6:19
88/3/2023 7:08user129test7user129|test7|8/3/2023 7:08
98/3/2023 7:08user130test8user130|test8|8/3/2023 7:08
108/3/2023 7:08user131test9user131|test9|8/3/2023 7:08
118/3/2023 7:09user132test10user132|test10|8/3/2023 7:09
128/3/2023 7:10user1331VNA23user133|1VNA23|8/3/2023 7:10
138/3/2023 7:11user134test12user134|test12|8/3/2023 7:11
148/3/2023 8:45user1352VNA34user135|2VNA34|8/3/2023 8:45
158/3/2023 8:45user1331VNA23user133|1VNA23|8/3/2023 8:45
168/3/2023 8:46user1331VNA23user133|1VNA23|8/3/2023 8:45
178/3/2023 8:47user1331VNA23user133|1VNA23|8/3/2023 8:45
188/3/2023 9:17user1352VNA34user135|2VNA34|8/3/2023 8:45
198/3/2023 9:17user140test18user140|test18|8/3/2023 9:17
208/3/2023 9:18user141test19user141|test19|8/3/2023 9:18
218/3/2023 9:18user142test20user142|test20|8/3/2023 9:18
228/3/2023 9:26user143test21user143|test21|8/3/2023 9:26
238/3/2023 9:27user144test22user144|test22|8/3/2023 9:27
249/3/2023 8:45user1352VNA34user135|2VNA34|9/3/2023 8:45
259/3/2023 8:50user1352VNA34user135|2VNA34|9/3/2023 8:45
Mark Dupes
Cell Formulas
RangeFormula
G2:G25G2=B2&"|"&E2&TEXT(MINIFS(A$2:A$25,B$2:B$25,B2,E$2:E$25,E2,A$2:A$25,">="&A2-1/24,A$2:A$25,"<="&A2+1/24),"|d/m/yyyy h:mm")
 
Upvote 0
Peter_SSs said:
Hmm, the "To Location" repeat was looming as my next question.

This seems to be one way and then use column G only as the remove duplicates criterion.

moddddmyexcel.xlsm
ABEG
1Date/TimeUser IDTo LocationHelper
28/3/2023 6:16user123test1user123|test1|8/3/2023 6:16
38/3/2023 6:16user124test2user124|test2|8/3/2023 6:16
48/3/2023 6:17user125test3user125|test3|8/3/2023 6:17
58/3/2023 6:17user135AAAuser135|AAA|8/3/2023 6:17
68/3/2023 6:18user135AAAuser135|AAA|8/3/2023 6:17
78/3/2023 6:19user128test6user128|test6|8/3/2023 6:19
88/3/2023 7:08user129test7user129|test7|8/3/2023 7:08
98/3/2023 7:08user130test8user130|test8|8/3/2023 7:08
108/3/2023 7:08user131test9user131|test9|8/3/2023 7:08
118/3/2023 7:09user132test10user132|test10|8/3/2023 7:09
128/3/2023 7:10user1331VNA23user133|1VNA23|8/3/2023 7:10
138/3/2023 7:11user134test12user134|test12|8/3/2023 7:11
148/3/2023 8:45user1352VNA34user135|2VNA34|8/3/2023 8:45
158/3/2023 8:45user1331VNA23user133|1VNA23|8/3/2023 8:45
168/3/2023 8:46user1331VNA23user133|1VNA23|8/3/2023 8:45
178/3/2023 8:47user1331VNA23user133|1VNA23|8/3/2023 8:45
188/3/2023 9:17user1352VNA34user135|2VNA34|8/3/2023 8:45
198/3/2023 9:17user140test18user140|test18|8/3/2023 9:17
208/3/2023 9:18user141test19user141|test19|8/3/2023 9:18
218/3/2023 9:18user142test20user142|test20|8/3/2023 9:18
228/3/2023 9:26user143test21user143|test21|8/3/2023 9:26
238/3/2023 9:27user144test22user144|test22|8/3/2023 9:27
249/3/2023 8:45user1352VNA34user135|2VNA34|9/3/2023 8:45
259/3/2023 8:50user1352VNA34user135|2VNA34|9/3/2023 8:45
Mark Dupes
Cell Formulas
RangeFormula
G2:G25G2=B2&"|"&E2&TEXT(MINIFS(A$2:A$25,B$2:B$25,B2,E$2:E$25,E2,A$2:A$25,">="&A2-1/24,A$2:A$25,"<="&A2+1/24),"|d/m/yyyy h:mm")
Click to expand...
I may not get a chance to dig into this till Monday but this looks like a great solution! I will keep you posted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
124
flashgordie
F
H
  • Solved
Getting the last price for each duplicates ID
Replies
2
Views
143
Hasson
H
A
  • Solved
Macro getting stuck on average when no data is present
Replies
5
Views
429
amphead
A
F
  • Question Question
Counting when record falls within a dynamically updating date range, additional matching criteria
Replies
1
Views
540
awoohaw
awoohaw
F
  • Question Question
Count # of values that fall within a certain date range
Replies
1
Views
575
RobP
R

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top