• Excel – remove duplicates

    • This topic has 20 replies, 4 voices, and was last updated 4 months ago.

    I hope all are well.

    What is the “easiest” way to remove duplicates in Excel?

    A macro, formula or even an application would be nice.

    I manage a newsletter mailing list (emails) and it can be an absolute nightmare. Need a better way.

    Cannot afford a monthly or very expensive option.

    TIA – BJ

    Viewing 13 reply threads
    • #2511070

      What sort of duplicates ? One column or many ?

      One quick and easy way to remove duplicates is to sort the data and either

      1. Do a visual inspection for duplicates as they will now be adjacent
      2. Add a column with an IF statement testing if the two entries on the same row and the one below are the same, thus finding any duplicates

      Do you have a sample workbook ?

    • #2511077

      Hope all is well; appreciate.

      Actually I think I didn’t explain properly; I try here; issue here is to deal with “removal” of emails (we have a few hundred emails and need to remove multiple emails at one time. Or if with some automation can also work well.

      Attached a sample – my challenge is to manage a few hundred emails and bulk compares.

      • Column A – the exemplar has 3 emails of people who want to unsubscribe
      • Column B – has the actual mailing list

      We want to compare Column A with B and “delete” these 3 automatically somehow.

      Thank you.

    • #2511098


      I’ve attached V2 of your sample workbook that goes some way towards your goal.

      It has a macro RemoveUnwantedEmailAddresses which deletes any addresses in your unsubscribe list.

      It will deal with any number of addresses and unsubscribe requirements as long as there are no gaps in either list

      Note that I have given two of the worksheet cells Names – these Names must be present for the macro to run. You can see the Names at the top left of the worksheet when you click on either cell:

      A2:  “UnsubscribeListStart”

      B2: “SubscriptionListStart”

      If you want to close-up the blanks that the macro produces it may be simplest to to that as follows:

      1. Select the range from which you want to remove blanks
      2. Press F5 and click the Special button
      3. In the Go To Special dialog box, select Blanks and click OK
      4. Right-click any one of the selected blanks, and choose Delete from the context menu:

      Hope this helps

    • #2511114

      I thought I had attached it !
      Here it is . . .

    • #2511126

      Yes, I’m not being allowed to attached it, despite it being in the list of allowed file types !

      Never mind, let’s try it this way:

      1. Open the workbook you sent me and save it as a Macro-enabled Workbook – file type .xlsm
      2. Close it and then re-open it
      3. Click on cell A2 and in the box at the top left (which should be displaying A2), type UnsubscribeListStart and press return
      4. Click on cell B2 and in the box at the top left (which should be displaying B2), type SubscriptionListStart and press return
      5. Create a new Macro: Press ALT and F11
      6. Double click on ThisWorkbook at the left
      7. Copy the bold text below into the blank screen which opens, then close the whole window
      8. Press ALT and F8 to bring up the list of macros.
      9. Pick RemoveUnwantedEmailAddresses() and click on Run
      10. Now your duplicates should be gone, replaced by blank cells

      Sub RemoveUnwantedEmailAddresses()

      Dim UnsubscribeListRow As Integer ‘To track which address is being unsuscribed
      Dim SubscriberListRow As Integer ‘To track where we are in the list of subscribers’ emails

      UnsuscribeListRow = 0 ‘Initialise

      Do While Range(“UnsubscribeListStart”).Offset(UnsubscribeListRow, 0).Value <> “” ‘Keep going while there are addresses to unsuscribe

      SubscriberListRow = 0

      Do While Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value <> “” ‘Keep going while there are addresses to unsuscribe

      ‘Test for a duplicate valee

      If Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = Range(“UnsubscribeListStart”).Offset(UnsubscribeListRow, 0).Value Then

      Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = “Deleted” ‘Remove the duplicate

      End If

      SubscriberListRow = SubscriberListRow + 1


      UnsubscribeListRow = UnsubscribeListRow + 1


      ‘Now removed any “Deleted” values

      SubscriberListRow = 0

      Do While Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value <> “” ‘Run through the email list once

      ‘Test for a “Deleted” valee

      If Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = “Deleted” Then

      Range(“SubscriptionListStart”).Offset(SubscriberListRow, 0).Value = “” ‘Remove “Deleted” value

      End If

      SubscriberListRow = SubscriberListRow + 1


      End Sub

    • #2511146

      Susan here – try changing the file name to .renamemetoxlsm and see if it let’s you upload?

      Susan Bradley Patch Lady/Prudent patcher

      1 user thanked author for this post.
    • #2511209

      Here we go again – if this works, simply rename the file from .docx to .


      Fingers crossed !

    • #2511211

      Trying again !

      Rename this file from .pdf to .xlsm

    • #2511582

      many sites have issues that block uploads. Here is a workaround that works for most.

      • create a zip file of the xlsm.
        • IE work.xlsm created to work.zip
      • rename a zip file of the pdf.
        • IE work.zip created to work.pdf
      • create another zip and than upload
        • IE work.pdf create a zip

      The person than has to do things backwards  in list above to get the file back. A long process but it has 99% chance of working. It worked on all site that I use that block file types.

    • #2599405

      The forum software removes it (maybe a security thing); here you go: https://bit.ly/screenshot1st

      Thank you.

    • #2599408

      That is the same as the one you posted and it is not an error message, it is the macro in the editor.
      Exactly what message do you get when you attempt to run it?

      cheers, Paul

    • #2599420

      Matie – here you go.

    • #2599433

      I don’t have Excel so I’m guessing that you pasted direct from here and the formatting applied has mucked up the code.
      It looks like the comments are the issue – you need an apostrophe and the site has made it an accent grave. I would copy one AG (in front of “To track which address…”), paste it into a replace dialog and replace all with an apostrophe.

      cheers, Paul

    • #2599440

      Without Excel I can’t really help more. Maybe someone else will chime in.

      cheers, Paul

    Viewing 13 reply threads
    Reply To: Excel – remove duplicates

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: