Combine duplicate rows with same email address


You have a different profiles (and rows on your export) for people with the same email address (ie a profile for John Smith and another for Mary Smith but they both share the same email address johnandmary@gmail.com).

PIC WITH EXAMPLE.


First make sure you have a fixed header row:

In order to lock excel header row, open the Microsoft Excel document. 

  1. Go to "view" tab. Click on "Freeze Panes" and select "Freeze Top Row". Now when you scroll your spreadsheet a header row will always be visible at the top.


You will need to use Custom First Names

If you haven't already duplicate the entire first name column and call the new column "Custom First Name"

  1. Select the column > Copy
  2. Insert > Copied Cells
  3. Rename new column: Custom First Name


Duplicate the sheet


  1. Right-Click (or Control-Click) the sheet tab > Move or Copy...
  2. [tick] Make a copy 
  3. Move to end
  4. Click on the new sheet and rename it duplicates
  5. Right-Click (or Control-Click) the sheet tab > Rename: Duplicates
  6. Freeze the top row

Delete non-duplicates

  1. In the Duplicate sheet, insert a column before the email column and name it: Dup=2
  2. In the first row under the header add this formula: =COUNTIF(X:X,Xi)
    1. Where X is the email column
    2. Where i is the first row under the header row
    3. eg: =COUNTIF(B:B,B2)
    4. Return (and you should see a number, eg: 1 or 2)

    5. Apply the formula to the whole column by double clicking the bottom right corner of the cell with the formula
    6. Fill handle
  3. Sort the list
    1. Select everything
    2. Data > A-Z
    3. Sort by Column: Dup=2
    4. +
    5. Then sort by Column: The email column
    6. [tick] My list has headers
    7. OK
  4. Select all rows containing a 1
    1. Delete Rows: Edit > Delete


Delete blanks

  1. In the Duplicate sheet, sort the name column 
    • Select everything
    • Data > A-Z
    • Sort by Column: First Name
    • [tick] My list has headers
    • OK
  2. Select all blank name rows
    1. Delete Rows: Edit > Delete

Check for triplicates (or more)


  1. Select everything
    1. Data > A-Z
    2. Sort by Column: Dup=2
    3. +
    4. Then sort by Column: The email column
    5. [tick] My list has headers
    6. OK
  2. Scroll down to Dup=2 column looking for rows with Dup=2 column being 3 or higher:
    1. You are aiming to have only "2" with each row having a different first name (but related)...
      1. So remove any rows with duplicates of the same first name (or with no first name)
      2. REMOVE row(s) where the names appear to NOT be partners (ie KEEP two with the same last name)
      3. Now all Dup=2 column should be "2" (or "1")
      4. If there are "1"s then KEEP them!!!


Clear Birthdays (if applicable) for "2"s only


If you have BirthdayMail and birthdates are included in the spreadsheet you must remove them for couples

  1. Select the cells in the date row (BUT NOT THE HEADER ROW)
    1. Scroll down to "2"s
    2. For the first "2" row click on the first Birthday cell
    3. CONTROL (COMMAND on Mac) +SHIFT+Down arrow
  2. Edit > Clear
  3. Add in a random Birthdate te to the 2nd row with Dup=2 (being "2"). 
    1. This row will not be imported but is needed for the import to work later on - weird, I know.


Create combined first names

Eg John & Mary


You should still be in the duplicate sheet and have all duplicate rows in consecutive pairs, eg

Email                                        First Name    Custom First Name

johnandmary@gmail.com     John                John

johnandmary@gmail.com    Mary                Mary


  1. Add this formula in the Custom First Name column starting at the first row where Dup=2 is "2"
  2. =IF(Bi=Bii,Ci&" & "&Cii,"")
  3. Where B is the email column and is the first name column and is the first row where Dup=2 is "2" and ii is the next row down
  4. Duplicate the formula to the entire column by double clicking the bottom right corner of the cell with the formulaFill handle


Export/Import into FeedsyMailHQ


  1. Save as > CSV format
  2. Upload the original sheet CSV into FeedsyMailHQ (eg your FeedsyList list) as normal (including Custom First Name, Birthday, etc)
  3. Upload the "Duplicates" sheet into FeedsyMailHQ
    1. Note: the second upload will override the first upload and remove blanks, add the combined first names and remove the birthdays for couples











Did you find it helpful? Yes No

Can you please tell us how we can improve this article?