Remove Rows Based on Partial Duplicates For Email Marketing Like Mail Chimp

  • Hi,

    I have three Excel lists from three different sources (online sign ups, texts, in the office). I am preparing a Mail Chimp email campaign but wanted to clean up the list before uploading it. I am not very savvy with VBA so a formula would be ideal. The formula could get inserted in the last column (E) to distinguish what rows to delete.

    The mailing list will include First Name | Last Name | Email | Phone

    PROBLEM: Since I combined 3 lists, I have duplicate entries. The only column that I want to use to find the duplicate entries is the Email column as that is unique to each customer I have.

    SOLUTION: I am seeking a formula that looks through the Email column, finds duplicate emails and removes the entire row no matter what else is in that row. The attached PNG explains my inquiry visually.

    Thank you in advance!


    "The attached Spreadsheet has dummy /made up information to protect my clients data."

  • Hi and Welcome to the Forum :)

    A formula will never remove duplicate rows ...;)

    Regarding the formula you are looking for in order to identify duplicates ... you can test in cell E2 :


    Copying this formula down to row 15, each time the result shows 2 ... would be a duplicate row to be deleted ...

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim,

    This worked wonderfully. What a "time saver" formula. Thank you!

    A little side note for those who will use the same formula. If there are more than 2 of the same emails, you will see 3, 4, 5 in the last column. Just go to Data Tab -> Filter and uncheck "1" from the list. Then every row that will stay, you can remove.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!