Removing Duplicate Data in VBA

New Contributor

I posted this on another site's forum but haven't gotten any response and I'm hoping someone here may know the answer.

 

I need to remove overlapping data. I was given this formula and instructions on how to use it. Here are the instructions:

  1. Paste this formula into column L2, =D2&"~"&B2&"~"&DAY(G2)&MONTH(G2)&YEAR(G2)&"~"&TEXT(TIME(HOUR(G2),0,0),"hh:mm:ss AM/PM")
  2. Sort the spreadsheet by the new column and then by coverage Greatest to Smallest - This is so when we remove duplicates it will keep the record with the largest coverage value.
  3. On the data tab in excel click on remove duplicates and uncheck all columns except the new one we made in step 1 then push ok.


The problem is if the overlapping time crosses into the next hour. Because formula pulls the hour from G2 into L2, when I remove the duplicates, these overlapping entries aren't removed.

  • Jane Doe: The calculated end time (Insert Date + Watch Time) is 11/29/2021 16:00:42, which is greater than the time in G3 (15:34:59).
  • John Miller: The calculated end time (Insert Date + Watch Time) is 03/28/2022 19:47:53, which is greater than the time in G5 (19:06:48).
  • Mary Thomas: The calculated end time (Insert Date + Watch Time) is 03/22/2022 18:48:03, which is greater than the time in G7 (18:04:53).

How can I remove the duplicates including those when the Insert Time crosses into the next hour? Doesn't have to be this formula either; whatever will work best and produce the result I need.

 

    Range("L1").Formula2R1C1 = "Duplicate"
    Range("L2").Formula2R1C1 = "=RC[-8]&""~""&RC[-10]&""~""&DAY(RC[-5])&MONTH(RC[-5])&YEAR(RC[-5])&""~""&TEXT(TIME(HOUR(RC[-5]),0,0),""hh:mm:ss AM/PM"")"
    Columns("L:L").EntireColumn.AutoFit
    With ActiveSheet.ListObjects("Table1").Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("Table1[Duplicate]"), Order:=xlAscending
        .Header = xlYes
        .Apply
    End With
    ActiveSheet.Range("Table1[#All]").RemoveDuplicates Columns:=12, Header:=xlYes

Tracking IDCase IDCase TitleUser IDFull NameInsert DateLast WatchedCoverageVideo Length (hh:mm:ss)Watch Time (hh:mm:ss)DuplicateCalculated End Date
9597313Unmasking Racial Inequality in Healthcare Video29496Jane Doe11/29/2021 14:58:4011/29/2021 14:58:40100.000%01:02:0201:02:0229496~313~29112021~02:00:00 PM11/29/2021 16:00:42
9596313Unmasking Racial Inequality in Healthcare Video29496Jane Doe11/29/2021 15:34:5911/29/2021 15:34:59100.000%01:02:0201:02:0229496~313~29112021~03:00:00 PM 
17638313Unmasking Racial Inequality in Healthcare Video29869John Miller03/28/2022 18:57:1703/28/2022 18:57:1781.547%01:02:0200:50:3629869~313~2832022~06:00:00 PM03/28/2022 19:47:53
17642313Unmasking Racial Inequality in Healthcare Video29869John Miller03/28/2022 19:06:4803/28/2022 19:06:4814.827%01:02:0200:09:1229869~313~2832022~07:00:00 PM 
16544313Unmasking Racial Inequality in Healthcare Video9104Mary Thomas03/22/2022 17:54:3803/22/2022 17:54:3886.086%01:02:0200:53:259104~313~2232022~05:00:00 PM03/22/2022 18:48:03
16547313Unmasking Racial Inequality in Healthcare Video9104Mary Thomas03/22/2022 18:04:5303/22/2022 18:04:5314.424%01:02:0200:08:579104~313~2232022~06:00:00 PM 
0 Replies