SOLVED

Sorting out duplicates.

Copper Contributor

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?

15 Replies

@lesleypohl 

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.

tempsnip.png

Hi, sorry I should further clarify.
I need both email addresses removed not just the duplicate? I have my main data, then I paste the unsubscribe data below it and highlight for duplicates to show me who needs to be removed out of the main list at the time of emailing.

@lesleypohl 

 

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.

@lesleypohl 

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],"")

tempsnip.png

YES!!!!!!! The formula won't work for me LOL but this is 100% exactly what I am talking about :D
Hi, well the data is coming from multiple platforms so unfortunately it's the only way until our new integration into salesforce.

@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.

best response confirmed by lesleypohl (Copper Contributor)
Solution

@lesleypohl 

Filter by color (From your conditional formatting), delete the visible rows and then remove the filter.

Wow I had no idea I could filter by color?? DUH!!! You have no idea how much work you've just saved me!!!!!! Thanks a million.

@SciGalSal 

 

Is there a way to highlight the duplicates prior to deleting them so I can verify the information?

@Thouse793 

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.

 

dups.pngdups highlighted.png

@SciGalSal 

 

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

@Thouse793 THANK YOU!!!!!!!!!!!

 

Who said you can't teach an OLD dog new tricks....you ROCK!!!!!!!!!!

@Thouse793 

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.

@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

1 best response

Accepted Solutions
best response confirmed by lesleypohl (Copper Contributor)
Solution

@lesleypohl 

Filter by color (From your conditional formatting), delete the visible rows and then remove the filter.

View solution in original post