Help needed deleting specific rows.

[thesmallfry123]

Hello there,

We are working on a research project pertaining to the Covid-19 virus. We need to curate a long list of anti-viral agents down to a manageable list. We have removed a few antiviral agents and would like to remove some more. List 1 (titled Worksheet 1 in the attached file) is what I would like to retain, it contains about 23,000 different anti-viral agents. The rest of the anti-viral agents have to be deleted from the bigger list ( list 2 titled Worksheet 2 in the attached file) containing roughly 30,000 anti-viral agents. Both of these lists have been entered in 2 different worksheets. List 2 contains the anti-viral agents from list 1 but it contains 7,000 additional anti-viral agents that I wont be needing. Hope the question is clear. There are around 30,000 rows so deleting the rows manually is not feasible. Is there any add-in or built-in Microsoft Excel feature that will let me delete the rows automatically.

Thank you for your time and consideration.

[davesexcel]

Select the top row of the data and select Data=>Filter, drop-down arrows will appear on the top row.

Click the arrow and select what you want to filter for. Lots of options and custom filters as well

Worth the practise.


dataFilter.jpg

[thesmallfry123]

Thank you for getting back to me. One thing I should have mentioned earlier is that, the list I would like to retain (worksheet 1) contains about 23,000 different anti-viral agents. The rest of the anti-viral agents have to be deleted from the bigger list containing roughly 30,000 anti-viral agents(Worksheet 2). List 2 contains the anti-viral agents from list 1 but it contains 7,000 additional anti-viral agents that I wont be needing. Hope the question is clear.

[bluesky63]

Hi, if you can attached a file and stated what you desire to get for your final reports, and I think this can be done using Power Query.

[thesmallfry123]

I have attached the excel file. List 1 (titled Worksheet 1 in the attached file) is what I would like to retain, it contains about 23,000 different anti-viral agents. The rest of the anti-viral agents have to be deleted from the bigger list ( list 2 titled Worksheet 2 in the attached file) containing roughly 30,000 anti-viral agents. Both of these lists have been entered in 2 different worksheets. List 2 contains the anti-viral agents from list 1 but it contains 7,000 additional anti-viral agents that I wont be needing. Hope the question is clear. There are around 30,000 rows so deleting the rows manually is not feasible.
Thank you once again.

[bluesky63]

Hi,

Bring in Worksheet1 and Worksheet2 to Power Query Editor, Merge both of them as New, filter those not in Worksheet1, load to excel in Final worksheet

If this is what you want and a simple way

[Mumps1]

Try:

Sub CompareLists()
    Application.ScreenUpdating = False
    Dim rng As Range, RngList As Object, WS1 As Worksheet, WS2 As Worksheet, LastRow As Long, x As Long
    Set WS1 = Sheets("worksheet 1")
    Set WS2 = Sheets("worksheet 2")
    LastRow = WS2.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each rng In WS1.Range("A2", WS1.Range("A" & WS1.Rows.Count).End(xlUp))
        If Not RngList.Exists(rng.Value) Then
            RngList.Add rng.Value, Nothing
        End If
    Next
    For x = LastRow To 2 Step -1
        With WS2
            If Not RngList.Exists(.Cells(x, 1).Value) Then
                .Rows(x).Delete
            End If
        End With
    Next
    Application.ScreenUpdating = True
End Sub

[AliGW]

This will undoubtedly require VBA, so I've taken the liberty of moving your thread to the VBA section.

[thesmallfry123]

thank you all for the help. I have managed to perform the task successfully. Thanks once again for the prompt aid.