moddddmyexcel
New Member
- Joined
- Mar 6, 2023
- Messages
- 23
- Office Version
- 365
- Platform
- 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 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Date/Time | User ID | Load Number | From Location | To Location | Quanity | ||
2 | 3/8/2023 6:16 | user123 | L00066470477 | LSAET1102 | test1 | 102 | ||
3 | 3/8/2023 6:16 | user124 | L00066470478 | LSAET1102 | test2 | 81 | ||
4 | 3/8/2023 6:17 | user125 | L00066470480 | LSAET1102 | test3 | 382 | ||
5 | 3/8/2023 6:17 | user126 | GCO0000293872 | LSAET1102 | test4 | 1344 | ||
6 | 3/8/2023 6:18 | user127 | L00066470481 | LSAET1102 | test5 | 58 | ||
7 | 3/8/2023 6:19 | user128 | 1.01959E+17 | LSAET1102 | test6 | 1000 | ||
8 | 3/8/2023 7:08 | user129 | L00066470579 | LSAET1102 | test7 | 114 | ||
9 | 3/8/2023 7:08 | user130 | L00066470581 | LSAET1102 | test8 | 118 | ||
10 | 3/8/2023 7:08 | user131 | GCO0000298623 | LSAET1102 | test9 | 90 | ||
11 | 3/8/2023 7:09 | user132 | L00066470585 | LSAET1102 | test10 | 54 | ||
12 | 3/8/2023 7:10 | user133 | L00066470586 | LSAET1102 | 1VNA23 | 121 | ||
13 | 3/8/2023 7:11 | user134 | L00066470587 | LSAET1102 | test12 | 73 | ||
14 | 3/8/2023 8:45 | user135 | GCO0000293437 | LSAET1102 | 2VNA34 | 1632 | ||
15 | 3/8/2023 8:45 | user133 | L00066470752 | LSAET1102 | 1VNA23 | 130 | ||
16 | 3/8/2023 8:46 | user133 | L00066470753 | LSAET1102 | 1VNA23 | 76 | ||
17 | 3/8/2023 8:47 | user133 | 1.01959E+17 | LSAET1102 | 1VNA23 | 1000 | ||
18 | 3/8/2023 9:17 | user135 | L00066470836 | LSAET1102 | 2VNA34 | 51 | ||
19 | 3/8/2023 9:17 | user140 | L00066470845 | LSAET1102 | test18 | 55 | ||
20 | 3/8/2023 9:18 | user141 | L00066470858 | LSAET1102 | test19 | 67 | ||
21 | 3/8/2023 9:18 | user142 | L00066470889 | LSAET1102 | test20 | 167 | ||
22 | 3/8/2023 9:26 | user143 | L00066471063 | LSAET1102 | test21 | 56 | ||
23 | 3/8/2023 9:27 | user144 | L00066471076 | LSAET1102 | test22 | 5 | ||
Sheet1 |