VBA to Create Table and to Remove Duplicates

[jessebh2003]

Hello,

I have two items for my data where I could use some help.

Item 1
The data needs turned into a table but the number of rows varies (the columns are always the same). I used to macro recorder for these and tweaked them.

First I tried the below but the code locked in the range.

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$13200"), , xlYes).Name = "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight13"
    ActiveSheet.ListObjects("Table1").ShowTableStyleRowStripes = False
    Rows("1:1").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0

Then I tried selecting the cells in row 1 for each column and doing Ctrl+Shift+Down but the code locked in the range.

    Range("A1:K1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$13200"), , xlYes).Name = "Table1"

I looked online and watched videos and much of it described writing code to actually create the table from scratch but that didn't seem right. Is there an easier way?

Item 2
In this same table, 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

Copy of Video Tracking Report-original cleaned.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Tracking ID	Case ID	Case Title	User ID	Full Name	Email	Insert Date	Last Watched	Coverage	Video Length (hh:mm:ss)	Watch Time (hh:mm:ss)	Duplicate	Calculated End Date
2	9597	313	Unmasking Racial Inequality in Healthcare Video	29496	Jane Doe	Jane.Doe@noemail.com	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
3	9596	313	Unmasking Racial Inequality in Healthcare Video	29496	Jane Doe	Jane.Doe@noemail.com	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
4	17638	313	Unmasking Racial Inequality in Healthcare Video	29869	John Miller	John.Miller@noemail.com	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
5	17642	313	Unmasking Racial Inequality in Healthcare Video	29869	John Miller	John.Miller@noemail.com	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
6	16544	313	Unmasking Racial Inequality in Healthcare Video	9104	Mary Thomas	Mary.Thomas@noemail.com	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
7	16547	313	Unmasking Racial Inequality in Healthcare Video	9104	Mary Thomas	Mary.Thomas@noemail.com	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
Sheet1

Cell Formulas
Range	Formula
M2,M6,M4	M2	=G2+K2
L2:L7	L2	=D2&"~"&B2&"~"&DAY(G2)&MONTH(G2)&YEAR(G2)&"~"&TEXT(TIME(HOUR(G2),0,0),"hh:mm:ss AM/PM")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2	Cell Value	>$H$3	text	NO
M4	Cell Value	>$H$5	text	NO
M6	Cell Value	>$H$7	text	NO

I appreciate any help! Thank you so much.