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.
Hi Hussain,
If you only want to keep 13 rows why not copy/paste these somewhere safe, delete the entire 2nd list, then cut/paste the 13 rows back again?
Or, format the 2nd list as a table, filter out the 13 rows you want to keep (so they don't display), then delete all remaining rows, then remove filters to show your 13 rows.
Regards
Phil
Hi Hussain,
I don't think your question is quite clear enough. Presumably the bigger list won't have just 13 rows remaining and that there will be multiple instances of the rows that you want to keep. If so, how do you identify rows in your bigger list that match rows in the list you want to keep?
You could use Power Query to automate this if you can explain how Excel would identify the rows to keep in the bigger list. e.g. I'm assuming there is a common identifier in both lists that you can use to find rows to keep.
Mynda
Thank you for getting back to me. Sorry, I didn't explain the question properly. The list (lets name this list as "list 1") I would like to retain contains about 23,000 different anti-viral agents. The rest of the anti-viral agents have to be deleted from the bigger list (this will be "list 2") 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.
Thank you once again,
Hussain.
Hello Mynda,
there is no identifier. The rows to keep are in a random order. For instance, out of the first 100 I need the rows 60,61,62,63,66 and then i need the row 107, 116, 120,122 and so on. The rows to keep were identified used a software called padel descriptor which calculates the descriptors for the different anti-viral agents. Do you think power query would be able to perform this task for me?
thank you.
Hello,
Power Query operates on given instructions, so it needs data that identifies what rows to keep. You seem to be able to tell as you describe what rows to keep, so how do you identify those? You write that you have a list containing rows you want to keep, how did you get that list?
If you can give PQ the logic on how to identify what data to keep then you have a tool that can automate the data removal task, else not.
But a question, if you now already have a list containing data of the rows to keep, why don’t you work from that list?
If it is so that you get that data from another source and have imported it to Excel, then I suggest you use PQ to import the data for you, for example from files in a folder. In this way you can build and update a list of needed data, no need to remove rows.
Br,
Anders
Hussain
If i have read your questuion right do you just need to compare one long list against another? If so you could just use the match funtion - see attached.
It is always better to add an example workbook that screen dumps so we can see how the data is laid out
thank you all for the help. I have managed to perform the task successfully. Thanks once again for the prompt aid.
