Comparing Contact Lists In CSV File Format Using Excel

Why is this needed?

You may run into a situation where you see some of your contacts have failed uploading however, some may have been successful. Isolating which contacts failed, and why, may be tricky without this method.  

This procedure will allow you to separate the emails that failed upload to further troubleshoot any formatting issues within those addresses.

Another use case could be when you see a lower amount of requests than contacts on the list you sent to within your marketing campaign. This indicates some of the addresses were dropped prior to sending and the only way to find those addresses would be to compare the original sending list to those of the unsubscribe list to see which addresses may be on both.

Do I need to use Microsoft Excel?

While these steps may be achievable using Apple Numbers or Google Sheets, we find it works best and is most simple in Microsoft Excel.

**For this example we will be using the 2022 Office 365 version.

Steps to troubleshoot: 

1. Open the first CSV list (list 1) in Microsoft Excel.  
   1. If you need to export a list you can do this by going to your contact list within the Sendgrid UI Marketing > Contacts > YourList> Export CSV in the top right.
   2. If checking against unsubscribes it will be Suppressions > Global Unsubscribes > Settings Gear > Export CSV.
2. Open the second CSV list (list 2) in a new Microsoft Excel window/spreadsheet. 
3. Clear all but column 1 containing the emails in each sheet.
4. Copy the entire column on sheet 2 onto sheet 1.  You can do this by clicking on the ‘A’ at the top on sheet 2 and using CMD/CTRL + C to copy.
5. You will then paste into column ‘B’ in sheet 1.  You can do this by clicking on the ‘B’ at the top of the sheet then using CMD/CTRL + V to paste. Once done you should have 2 lists of emails side by side in 2 columns:
   
6. You will then want to highlight both columns fully.  When that is done click on the conditional formatting icon in the toolbar. You will want to select Conditional formatting > Highlight Cells Rule > Duplicate Values.
   
   
7. You can just click ok when the pop up arises. This will then highlight all duplicate values!

**Additionally you can sort the non-highlighted values to the top by highlighting the column with the non-highlighted emails and going to Sort&Filter in the toolbar and selecting Custom Sort.  You can then set the ‘Sort on’ property to Cell Color as shown below to move all the impacted emails to the top.

Related Documents: 

• Formatting A CSV