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. The list (Worksheet 1) labelled below as “List I want to retain” are the rows I would like to not delete from the bigger list (Worksheet 2) containing all the anti-viral agents but I need to delete the remaining rows from the bigger list. 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.

 

[thesmallfry123]

• download my file from post #6
• open this file
• click Show Queries / Connections on Data tab
• on the right side you should see pane where you can click Queries
• then double click on the Table there and it will open new window Power Query Editor

the query you provided did the task I was looking for correctly. One question I have is how I can change source 2 and replace a different worksheet with greater number of rows as source 2. Below is the worksheet I would like to use as source to perform the same task as we did with the previous worksheet.
Google Drive Download Link for the Worksheet.

Thank you for the help.

 

[thesmallfry123]

@thesmallfry123
Any response to my question in post #5?

For instance after running the macro the worksheet contains rows such as 50,55, 65,95 and so on that weren't in the worksheet1.

 

[sandy666]

the query you provided did the task I was looking for correctly. One question I have is how I can change source 2 and replace a different worksheet with greater number of rows as source 2. Below is the worksheet I would like to use as source to perform the same task as we did with the previous worksheet.
Google Drive Download Link for the Worksheet.

Thank you for the help.

Just copy'n'paste data then refresh green table

s666-PQ-Worksheets_v2.xlsx

Microsoft Excel Workbook

1drv.ms

 

[Peter_SSs]

For instance after running the macro the worksheet contains rows such as 50,55, 65,95 and so on that weren't in the worksheet1.

Have a look in Worksheet1 at cells
A23297 (50)
A20124 (55)
A22642 (95)

For row65 I'm not sure how it ended up in worksheet2 as it wasn't in worksheet 2 in the first place as far as I can see in the file I downloaded.

 

[thesmallfry123]

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

 

[Peter_SSs]

You're welcome. Thanks for letting us know.