Apr 02 2020 11:42 AM
Hello I have a big list of data (email subscribers) and I need to remove the unsubscribers. I do know how to use conditional formatting and highlight duplicates but how do I remove them without having to do it manually?
Apr 02 2020 11:56 AM
With all of your data in a column (ideally a table), select a single cell in the column with the duplicates. From the Data ribbon, use the Remove Duplicates button. It will prompt for acceptance of the current column and in on click your duplicates will be removed.
Apr 02 2020 12:11 PM
Apr 02 2020 12:45 PM
This last clarification raises (in my mind at any rate) another question, having to do with the way you've organized your database. You wrote "I have my main data, then I paste the unsubscribe data below it and highlight for duplicates..." making me think you're creating multiple rows for the same individual....which in general isn't a good idea.
So would it be possible for you to post an example of your actual spreadsheet (just rendering the names and any other identifiable things so they're not real ones?
That aside, I wonder why you feel a need to delete the unsubscribers in the first place. You obviously need to honor their request, but could that not be accomplished by a column in the database that indicates "Active" or "Unsubscribed"--that would then enable you to send a "Welcome back" note to anybody who eventually re-subscribes.
All of which is to say, there may be other ways altogether to manage this situation.
Apr 02 2020 12:46 PM
You could try this.
Put your full list in column A (table named Subscribers). Don't append the unsubscribers to the end, though, as you had been doing.
Put your unsubscribers in column C (table named Unsubscribe)
In Column E, use the following formula: =IF(ISNA(VLOOKUP(A2,C:C,1,FALSE)),Table1[@Subscribers],"")
Apr 02 2020 12:54 PM
Apr 02 2020 12:55 PM
Apr 02 2020 01:02 PM
@lesleypohl
Perhaps the formula isn't working because it's referencing the table by name. Try it replacing it with just the column label. Like: =IF(ISNA(VLOOKUP(A1,C:C,1,FALSE)),A1,"") for row 1, and let the A1 change as the formula is populated for each subsequent row.
Apr 02 2020 01:40 PM
SolutionFilter by color (From your conditional formatting), delete the visible rows and then remove the filter.
Apr 02 2020 02:42 PM
Aug 13 2020 09:38 AM
Is there a way to highlight the duplicates prior to deleting them so I can verify the information?
Aug 13 2020 09:45 AM
You can highlight both of the columns, and from the Data ribbon, select Conditional Formatting. From that dropdown menu, select Highlight Cell Rules > Duplicate Values. Once you confirm the color option it will show you the duplicates in both columns.
Aug 13 2020 09:53 AM
WOW...thanks for the speedy response. I have a spreadsheet of names for our holiday catalog. I read your post but I am a little confused (Sorry). Do I need to copy the address column to do the conditional formating?
Thanks in advance for your assistance. I attached a ship of my data list
Aug 13 2020 10:01 AM
@Thouse793 THANK YOU!!!!!!!!!!!
Who said you can't teach an OLD dog new tricks....you ROCK!!!!!!!!!!
Aug 13 2020 10:04 AM
If you just need to highlight the duplicates without comparing with another column, you can simply select the single column and check for duplicates that way. Then, with your data in a table, you can just sort by color and manually delete them. You alternatively could just use the Remove Duplicates option. This original post was a little different from your question, I think. But I'm happy to try and help.
Aug 17 2020 09:49 AM
@Thouse793 Who said you can't teach an OLD dog new tricks....you ROCK!!!!!!!!!!...
Sorry for delayed response...I was so excited I accidently sent response to wrong address
David
Apr 02 2020 01:40 PM
SolutionFilter by color (From your conditional formatting), delete the visible rows and then remove the filter.