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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
HuaMin ChenProblem resolverCommented:
Do you need to compare both lists or not?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.