SOLVED

Sorting out duplicates.

%3CLINGO-SUB%20id%3D%22lingo-sub-1276408%22%20slang%3D%22en-US%22%3ESorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276408%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20I%20have%20a%20big%20list%20of%20data%20(email%20subscribers)%20and%20I%20need%20to%20remove%20the%20unsubscribers.%20I%20do%20know%20how%20to%20use%20conditional%20formatting%20and%20highlight%20duplicates%20but%20how%20do%20I%20remove%20them%20without%20having%20to%20do%20it%20manually%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1276408%22%20slang%3D%22en-US%22%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-1276439%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606839%22%20target%3D%22_blank%22%3E%40lesleypohl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20all%20of%20your%20data%20in%20a%20column%20(ideally%20a%20table)%2C%20select%20a%20single%20cell%20in%20the%20column%20with%20the%20duplicates.%20From%20the%20Data%20ribbon%2C%20use%20the%20Remove%20Duplicates%20button.%20It%20will%20prompt%20for%20acceptance%20of%20the%20current%20column%20and%20in%20on%20click%20your%20duplicates%20will%20be%20removed.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tempsnip.png%22%20style%3D%22width%3A%20580px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181692i8A4AF30803BFE6A3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22tempsnip.png%22%20alt%3D%22tempsnip.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276470%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276470%22%20slang%3D%22en-US%22%3EHi%2C%20sorry%20I%20should%20further%20clarify.%3CBR%20%2F%3EI%20need%20both%20email%20addresses%20removed%20not%20just%20the%20duplicate%3F%20I%20have%20my%20main%20data%2C%20then%20I%20paste%20the%20unsubscribe%20data%20below%20it%20and%20highlight%20for%20duplicates%20to%20show%20me%20who%20needs%20to%20be%20removed%20out%20of%20the%20main%20list%20at%20the%20time%20of%20emailing.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276542%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276542%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606839%22%20target%3D%22_blank%22%3E%40lesleypohl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20last%20clarification%20raises%20(in%20my%20mind%20at%20any%20rate)%20another%20question%2C%20having%20to%20do%20with%20the%20way%20you've%20organized%20your%20database.%20You%20wrote%20%22I%20have%20my%20main%20data%2C%20then%20I%20%3CEM%3Epaste%20the%20unsubscribe%20data%20%3CU%3E%3CSTRONG%3Ebelow%3C%2FSTRONG%3E%3C%2FU%3E%20it%3C%2FEM%3E%20and%20highlight%20for%20duplicates...%22%20making%20me%20think%20you're%20creating%20multiple%20rows%20for%20the%20same%20individual....which%20in%20general%20isn't%20a%20good%20idea.%3C%2FP%3E%3CP%3ESo%20would%20it%20be%20possible%20for%20you%20to%20post%20an%20example%20of%20your%20actual%20spreadsheet%20(just%20rendering%20the%20names%20and%20any%20other%20identifiable%20things%20so%20they're%20not%20real%20ones%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20aside%2C%20I%20wonder%20why%20you%20feel%20a%20need%20to%20delete%20the%20unsubscribers%20in%20the%20first%20place.%20You%20obviously%20need%20to%20honor%20their%20request%2C%20but%20could%20that%20not%20be%20accomplished%20by%20a%20column%20in%20the%20database%20that%20indicates%20%22Active%22%20or%20%22Unsubscribed%22--that%20would%20then%20enable%20you%20to%20send%20a%20%22Welcome%20back%22%20note%20to%20anybody%20who%20eventually%20re-subscribes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20of%20which%20is%20to%20say%2C%20there%20may%20be%20other%20ways%20altogether%20to%20manage%20this%20situation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276547%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606839%22%20target%3D%22_blank%22%3E%40lesleypohl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20try%20this.%3C%2FP%3E%3CP%3EPut%20your%20full%20list%20in%20column%20A%20(table%20named%20Subscribers).%20Don't%20append%20the%20unsubscribers%20to%20the%20end%2C%20though%2C%20as%20you%20had%20been%20doing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPut%20your%20unsubscribers%20in%20column%20C%20(table%20named%20Unsubscribe)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Column%20E%2C%20use%20the%20following%20formula%3A%26nbsp%3B%3DIF(ISNA(VLOOKUP(A2%2CC%3AC%2C1%2CFALSE))%2CTable1%5B%40Subscribers%5D%2C%22%22)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22tempsnip.png%22%20style%3D%22width%3A%20598px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F181702i3C682DEE433BE36F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22tempsnip.png%22%20alt%3D%22tempsnip.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276579%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276579%22%20slang%3D%22en-US%22%3EYES!!!!!!!%20The%20formula%20won't%20work%20for%20me%20LOL%20but%20this%20is%20100%25%20exactly%20what%20I%20am%20talking%20about%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276582%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276582%22%20slang%3D%22en-US%22%3EHi%2C%20well%20the%20data%20is%20coming%20from%20multiple%20platforms%20so%20unfortunately%20it's%20the%20only%20way%20until%20our%20new%20integration%20into%20salesforce.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276598%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606839%22%20target%3D%22_blank%22%3E%40lesleypohl%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3EPerhaps%20the%20formula%20isn't%20working%20because%20it's%20referencing%20the%20table%20by%20name.%20Try%20it%20replacing%20it%20with%20just%20the%20column%20label.%20Like%3A%26nbsp%3B%20%3DIF(ISNA(VLOOKUP(A1%2CC%3AC%2C1%2CFALSE))%2CA1%2C%22%22)%26nbsp%3B%20%26nbsp%3Bfor%20row%201%2C%20and%20let%20the%20A1%20change%20as%20the%20formula%20is%20populated%20for%20each%20subsequent%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276728%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606839%22%20target%3D%22_blank%22%3E%40lesleypohl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFilter%20by%20color%20(From%20your%20conditional%20formatting)%2C%20delete%20the%20visible%20rows%20and%20then%20remove%20the%20filter.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1276890%22%20slang%3D%22en-US%22%3ERe%3A%20Sorting%20out%20duplicates.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1276890%22%20slang%3D%22en-US%22%3EWow%20I%20had%20no%20idea%20I%20could%20filter%20by%20color%3F%3F%20DUH!!!%20You%20have%20no%20idea%20how%20much%20work%20you've%20just%20saved%20me!!!!!!%20Thanks%20a%20million.%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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?

9 Replies
Highlighted

@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

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

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

Highlighted

@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

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

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

Highlighted
Solution

@lesleypohl 

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

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