Either/Or Options for Filtering (Removing a Contact with No Contact Info)

[lroseBSV]

Hi there,

I am working with a contact list of over 3,000 individuals. I need to remove people from the list who don't have either an address OR email. If they have one or the other, or both they remain on the list. Is this possible? My current method is to use the Ctrl+G option > Go To Special > Blanks, and cutting the cells that are blank in both the address and email columns. However, using this method will take a VERY long time to do with 3,000 contacts. Especially since every time I cut the row the highlighted blank cells disappear and I have to repeat the process, which kicks me back up to the 1st cell of the spreadsheet.

The second layer of complication is that I am not simply deleting rows with no email or address, but cutting and pasting them into another tab for our records. As I result I fear this might limit my options.

Please help! Thank you!!!

 

[lrobbo314]

Welcome to the forum.

Just add filters to your data. Hit Ctrl+Shift+L. Then for the email filter, only select blanks. Do the same for address. Then, you should have the results of the people without either. Then you can select the visible rows, i.e. click and drag on the row numbers, then hit Ctrl+X to cut, and Ctrl+V to paste on the other sheet.

 

[lroseBSV]

Welcome to the forum.

Just add filters to your data. Hit Ctrl+Shift+L. Then for the email filter, only select blanks. Do the same for address. Then, you should have the results of the people without either. Then you can select the visible rows, i.e. click and drag on the row numbers, then hit Ctrl+X to cut, and Ctrl+V to paste on the other sheet.

Of course! The answer was so obvious, thank you! The only snag I seem to be hitting is this: after filtering out to show everyone with no contact info, I drag and select the numbers on the left and paste into the new sheet. When I do this it appears that I am cutting all of the cells by mistake. When I go back to the first sheet to remove the filters there is no data. Any idea why this might be? ​thanks again

 

[lrobbo314]

Do this instead. Don't select the entire row. Just select the data that's visible. That is to say, select from A2:C12, or whatever your range is. Then hit F5. Click special, select visible cells only, then copy, paste to your other sheet. That will get the results to the other sheet. Then go back to your filtered data, now select the actual rows like you did before, then right click and click delete rows. Then remove your filters and you should be good to go.

Seems like quite a few steps, but it's not that bad.

If you're able to use VBA, and if this is something that you will be doing on a regular basis, I can write some code for you that will do the same thing, and it'll be a lot easier for you.