• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Help needed deleting specific rows.|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Help needed deleting specific rows.|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe… Help needed deleting specific rows…
sp_PrintTopic sp_TopicIcon
Help needed deleting specific rows.
Avatar
Hussain Hajji

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
July 17, 2020
sp_UserOfflineSmall Offline
1
July 17, 2020 - 3:46 am
sp_Permalink sp_Print

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.

Image Enlarger

Image Enlarger

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
July 17, 2020 - 8:50 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4447
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
3
July 17, 2020 - 9:00 am
sp_Permalink sp_Print

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

Avatar
Hussain Hajji

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
July 17, 2020
sp_UserOfflineSmall Offline
4
July 17, 2020 - 2:10 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Hussain Hajji

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
July 17, 2020
sp_UserOfflineSmall Offline
5
July 17, 2020 - 9:10 pm
sp_Permalink sp_Print sp_EditHistory

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.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
July 19, 2020 - 5:36 am
sp_Permalink sp_Print

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

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
7
July 19, 2020 - 6:56 am
sp_Permalink sp_Print

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

Avatar
Hussain Hajji

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
July 17, 2020
sp_UserOfflineSmall Offline
8
July 21, 2020 - 6:44 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Lynnette Altomari, Roy Lutke, Jeff Krueger, Natasha Smith, Monique Roussouw
Guest(s) 12
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Sopi Yuniarti
sandra parker
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6214
Posts: 27243

 

Member Stats:
Guest Posters: 49
Members: 31894
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.