VBA to remove duplicated lines from 2 tables pulled from a url

Hello.

on the attached ss the URL tab,  I have 2 tables being pulled from a url,  what i need to do is remove the lines in the second table  l18:t78 where they appear in the top 24 rows of the first table  B18:j78.
ASA--Swimming---2018-British---Home.xlsm
Stuart StoutProject managerAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here are two codes for two scenarios...

1) Table2 is compared with whole Table1.

Sub DeleteDuplicatesFromTable2()
Dim wsData As Worksheet
Dim tbl1, tbl2, arr, dict
Dim lr As Long, i As Long, ii As Long, j As Long, k As Long
Dim str1 As String, str2 As String

Application.ScreenUpdating = False

Set wsData = Sheets("URLs")
lr = wsData.Cells(Rows.Count, "B").End(xlUp).Row
tbl1 = wsData.Range("B19:J" & lr).Value

lr = wsData.Cells(Rows.Count, "L").End(xlUp).Row
tbl2 = wsData.Range("L19:T" & lr).Value

ReDim arr(1 To UBound(tbl2, 1), 1 To 9)

Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(tbl1, 1)
    For j = 2 To 9
        If str1 = "" Then
            str1 = tbl1(i, j)
        Else
            str1 = str1 & "_" & tbl1(i, j)
        End If
    Next j
    dict.Item(str1) = ""
    str1 = ""
Next i

For i = 1 To UBound(tbl2, 1)
    For j = 2 To 9
        If str2 = "" Then
            str2 = tbl2(i, j)
        Else
            str2 = str2 & "_" & tbl2(i, j)
        End If
    Next j
    
    If Not dict.exists(str2) Then
        ii = ii + 1
        For k = 1 To 9
            arr(ii, k) = tbl2(i, k)
        Next k
    End If
    str2 = ""
Next i
wsData.Range("L18").CurrentRegion.Offset(1).ClearContents
If ii > 0 Then
    wsData.Range("L19").Resize(ii, 9).Value = arr
End If
Application.ScreenUpdating = True
End Sub

Open in new window


2) Table2 is compared with top 24 Rows of Table1.

Sub DeleteDuplicatesFromTable2()
Dim wsData As Worksheet
Dim tbl1, tbl2, arr, dict
Dim lr As Long, i As Long, ii As Long, j As Long, k As Long
Dim str1 As String, str2 As String

Application.ScreenUpdating = False

Set wsData = Sheets("URLs")
lr = wsData.Cells(Rows.Count, "B").End(xlUp).Row
tbl1 = wsData.Range("B19:J19").Resize(24).Value

lr = wsData.Cells(Rows.Count, "L").End(xlUp).Row
tbl2 = wsData.Range("L19:T" & lr).Value

ReDim arr(1 To UBound(tbl2, 1), 1 To 9)

Set dict = CreateObject("Scripting.Dictionary")

For i = 1 To UBound(tbl1, 1)
    For j = 2 To 9
        If str1 = "" Then
            str1 = tbl1(i, j)
        Else
            str1 = str1 & "_" & tbl1(i, j)
        End If
    Next j
    dict.Item(str1) = ""
    str1 = ""
Next i

For i = 1 To UBound(tbl2, 1)
    For j = 2 To 9
        If str2 = "" Then
            str2 = tbl2(i, j)
        Else
            str2 = str2 & "_" & tbl2(i, j)
        End If
    Next j
    
    If Not dict.exists(str2) Then
        ii = ii + 1
        For k = 1 To 9
            arr(ii, k) = tbl2(i, k)
        Next k
    End If
    str2 = ""
Next i
wsData.Range("L18").CurrentRegion.Offset(1).ClearContents
If ii > 0 Then
    wsData.Range("L19").Resize(ii, 9).Value = arr
End If
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
HuaMin ChenProblem resolverCommented:
1. If the lines are not sorted well, you can presort the lines first in Excel

2. Then use a loop to scan through the list to find out duplicated lines.
0
 
Stuart StoutProject managerAuthor Commented:
hi, due to both tables being a qualification list that is not an option.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HuaMin ChenProblem resolverCommented:
Do you need to compare both lists or not?
0
 
Stuart StoutProject managerAuthor Commented:
I need to remove the line in the second table where it appears in the top 24 rows of table 1
0
 
Stuart StoutProject managerAuthor Commented:
the data in the tables is fluid and changes daily plus depends upon the selection made in rows 4, 6, 7 & 8
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If a row from table2 appears below row24 of table1, should it remain intact? i.e. not considered as a duplicate row?
0
 
Stuart StoutProject managerAuthor Commented:
yes, only remove from table 2 if it appears in the top 24 rows of table 1 .
0
 
HuaMin ChenProblem resolverCommented:
the data in the tables is fluid and changes daily plus depends upon the selection made in rows 4, 6, 7 & 8

You have to scan the list per your given conditions you already mentioned
0
 
Stuart StoutProject managerAuthor Commented:
Just the  job. Did exactly what was required many thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Stuart!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.