Issue
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.
Product
Email
Resolution
Do I need to use Microsoft Excel?
While these steps may be achievable using Apple Numbers or Google Sheets, it works best and simpler in Microsoft Excel.
Troubleshooting steps
**Using the 2022 Office 365 version for this example.
-
Open the first CSV list (list one) in Microsoft Excel.
- To export a list, go to the contact list within the Sendgrid UI Marketing > Contacts > YourList> Export CSV in the top right.
- If checking against unsubscribes it will be Suppressions > Global Unsubscribes > Settings Gear > Export CSV.
- Open the second CSV list (list two) in a new Microsoft Excel window/spreadsheet.
- Clear all but column one containing the emails in each sheet.
- Copy the entire column on sheet two onto sheet one. You can do this by clicking on the ‘A’ at the top on sheet two and using CMD/CTRL + C to copy.
- 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, there should be two lists of emails side by side in two columns:
-
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.
- Click ok when the pop up arises. This will then highlight all duplicate values!
Additionally, 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. Then set the ‘Sort by’ property to Cell Color as shown below to move all the impacted emails to the top. A 'Sort Warning' pop up will appear, check the "Expand the selection" and click on Sort to continue.