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:
- 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")
- 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.
- 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 ID | Case ID | Case Title | User ID | Full Name | Insert Date | Last Watched | Coverage | Video Length (hh:mm:ss) | Watch Time (hh:mm:ss) | Duplicate | Calculated End Date |
| 9597 | 313 | Unmasking Racial Inequality in Healthcare Video | 29496 | Jane Doe | 11/29/2021 14:58:40 | 11/29/2021 14:58:40 | 100.000% | 01:02:02 | 01:02:02 | 29496~313~29112021~02:00:00 PM | 11/29/2021 16:00:42 |
| 9596 | 313 | Unmasking Racial Inequality in Healthcare Video | 29496 | Jane Doe | 11/29/2021 15:34:59 | 11/29/2021 15:34:59 | 100.000% | 01:02:02 | 01:02:02 | 29496~313~29112021~03:00:00 PM | |
| 17638 | 313 | Unmasking Racial Inequality in Healthcare Video | 29869 | John Miller | 03/28/2022 18:57:17 | 03/28/2022 18:57:17 | 81.547% | 01:02:02 | 00:50:36 | 29869~313~2832022~06:00:00 PM | 03/28/2022 19:47:53 |
| 17642 | 313 | Unmasking Racial Inequality in Healthcare Video | 29869 | John Miller | 03/28/2022 19:06:48 | 03/28/2022 19:06:48 | 14.827% | 01:02:02 | 00:09:12 | 29869~313~2832022~07:00:00 PM | |
| 16544 | 313 | Unmasking Racial Inequality in Healthcare Video | 9104 | Mary Thomas | 03/22/2022 17:54:38 | 03/22/2022 17:54:38 | 86.086% | 01:02:02 | 00:53:25 | 9104~313~2232022~05:00:00 PM | 03/22/2022 18:48:03 |
| 16547 | 313 | Unmasking Racial Inequality in Healthcare Video | 9104 | Mary Thomas | 03/22/2022 18:04:53 | 03/22/2022 18:04:53 | 14.424% | 01:02:02 | 00:08:57 | 9104~313~2232022~06:00:00 PM | |
