Auto remove data from Excel sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-1879000%22%20slang%3D%22en-US%22%3EAuto%20remove%20data%20from%20Excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1879000%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3EFirst%20post%2C%20thank%20you%20for%20taking%20the%20time%20to%20read%20this.%20I%20am%20trying%20to%20do%20a%20couple%20things.%20I%20am%20a%20beginner%20at%20excel.%20I%20can%20do%20a%20vlookup%20and%20that%20is%20about%20it.%3C%2FP%3E%3CP%3EI%20spend%20lots%20of%20time%20extracting%20data%20from%20other%20sources%20and%20then%20trying%20to%20organize%20and%20filter%20the%20data.%20Lots%20of%20time%20spent%20filtering%20and%20removing%20names%20or%20objects%20based%20on%20different%20criteria.%20I%20got%20a%20book%20for%20Excel%202019%20here%20but%20I'm%20sure%20I%20am%20just%20looking%20in%20the%20wrong%20spot.%20Any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESituation%201)%3C%2FP%3E%3CP%3EFor%20example%2C%20right%20now%20I%20look%20manually%20for%20%3CA%20href%3D%22mailto%3ABob.Smith%40smith.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EBob.Smith%40smith.com%3C%2FA%3E%20to%20see%20if%20in%20column%20A.%20Then%20I%20remove%20that%20entire%20row.%20Next%20to%20the%20name%20might%20be%20location%20data%20so%20on.%20I%20can't%20just%20remove%20A1%20for%20example%20as%20A2%20to%20A7%20might%20have%20data%20that%20also%20relates%20to%20Bob%20and%20would%20need%20removing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20have%20been%20doing%20is%20pasting%20a%20list%20of%20the%20users%20I%20want%20to%20remove%20from%20a%20sheet%20or%20workbook%20in%20the%20column%20that%20the%20criteria%20matches.%20For%20example%20the%20email%20or%20name.%20Then%20doing%20a%20conditional%20formatting%20and%20highlighting%20the%20duplicates%20any%20color.%20Then%20just%20manually%20deleting%20those%20rows.%20Is%20there%20a%20better%20way%20to%20say%20list%20on%20another%20sheet%20and%20auto%20remove%20on%20the%20main%20sheet%20the%20rows%20beginning%20with%20that%20email%20or%20name%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESituation%202)%3C%2FP%3E%3CP%3EAnother%20thing%20I%20am%20doing%20is%20filtering%20out%20certain%20columns.%20For%20example%2C%20if%20the%20name%20has%20%22test%22%20or%20%22word%22%20or%20begins%20with%20%22JA.%22%20and%20removing%20those%20rows.%20As%20well%20as%20blanks.%20Similar%20to%20the%20above%20is%20there%20a%20better%20way%20to%20list%20these%20things%20so%20I%20can%20auto%20remove%20from%20a%20report%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESituation%203)%3C%2FP%3E%3CP%3ELast%20I%20do%20filtering%20of%20a%20column%20by%20does%20not%20contain%20%40smith.com%20for%20example.%20Is%20there%20a%20better%20way%20to%20go%20about%20cleaning%20this%20up%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20spend%20most%20of%20my%20weeks%20in%20these%20sheets%20and%20really%20not%20sure%20how%20to%20better%20the%20situation.%20I%20though%20about%20Microsoft%20Flow%20but%20i%20think%20Excel%20might%20just%20do%20what%20I%20am%20thinking%20automatically%20if%20I%20can%20configure%20the%20filters%20and%20settings%20or%20formulas.%20Any%20help%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1879000%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1882090%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20remove%20data%20from%20Excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1882090%22%20slang%3D%22en-US%22%3EHave%20you%20heard%20of%20Power%20Query%3F%20Sounds%20a%20lot%20like%20this%20would%20be%20a%20task%20for%20Power%20Query.%20I%20advise%20you%20to%20do%20some%20research%20to%20see%20if%20this%20can%20solve%20your%20issue.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1882828%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20remove%20data%20from%20Excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1882828%22%20slang%3D%22en-US%22%3ENo%2C%20this%20is%20a%20first.%20Looks%20like%20it%E2%80%99s%20built%20it.%20Any%20beginner%20suggestions%3F%20This%20does%20get%20me%20in%20the%20right%20direction%2C%20thank%20you%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fpower-query-overview-and-learning-ed614c81-4b00-4291-bd3a-55d80767f81d%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fpower-query-overview-and-learning-ed614c81-4b00-4291-bd3a-55d80767f81d%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1883383%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20remove%20data%20from%20Excel%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1883383%22%20slang%3D%22en-US%22%3EThis%20is%20an%20article%20I%20wrote%20about%20Power%20Query.%20It's%20Power%20Query%20in%20Power%20BI%20but%20it's%20the%20same%20thing%20in%20Excel.%20Also%2C%20there%20are%20lots%20of%20YouTube%20videos%20about%20Power%20Query%3C%2FLINGO-BODY%3E
New Contributor

Hello All,

First post, thank you for taking the time to read this. I am trying to do a couple things. I am a beginner at excel. I can do a vlookup and that is about it.

I spend lots of time extracting data from other sources and then trying to organize and filter the data. Lots of time spent filtering and removing names or objects based on different criteria. I got a book for Excel 2019 here but I'm sure I am just looking in the wrong spot. Any suggestions?

 

Situation 1)

For example, right now I look manually for Bob.Smith@smith.com to see if in column A. Then I remove that entire row. Next to the name might be location data so on. I can't just remove A1 for example as A2 to A7 might have data that also relates to Bob and would need removing.

 

What I have been doing is pasting a list of the users I want to remove from a sheet or workbook in the column that the criteria matches. For example the email or name. Then doing a conditional formatting and highlighting the duplicates any color. Then just manually deleting those rows. Is there a better way to say list on another sheet and auto remove on the main sheet the rows beginning with that email or name?

 

Situation 2)

Another thing I am doing is filtering out certain columns. For example, if the name has "test" or "word" or begins with "JA." and removing those rows. As well as blanks. Similar to the above is there a better way to list these things so I can auto remove from a report?

 

Situation 3)

Last I do filtering of a column by does not contain @smith.com for example. Is there a better way to go about cleaning this up?

 

I spend most of my weeks in these sheets and really not sure how to better the situation. I though about Microsoft Flow but i think Excel might just do what I am thinking automatically if I can configure the filters and settings or formulas. Any help is greatly appreciated.

 

 

 

3 Replies
Have you heard of Power Query? Sounds a lot like this would be a task for Power Query. I advise you to do some research to see if this can solve your issue.
No, this is a first. Looks like it’s built it. Any beginner suggestions? This does get me in the right direction, thank you

https://support.microsoft.com/en-us/office/power-query-overview-and-learning-ed614c81-4b00-4291-bd3a...
This is an article I wrote about Power Query. It's Power Query in Power BI but it's the same thing in Excel. Also, there are lots of YouTube videos about Power Query