I often work with large lists of email addresses when helping Mailchimp clients with their email marketing needs. Occasionally I need to identify unique values only in a list. For example a list of 100,000 contacts may have duplicate contacts and I need to quickly identify records where the email address isn’t duplicated.
Fortunately it is quick and simple in Microsoft Excel or Google sheets to identify and extract unique records. The instructions are below.
How to Select Unique Email Addresses in Excel and Google Sheets
Cost: Assuming you have Microsoft Excel or Google Sheets, this will cost you
$0
Time: 1 minute
Using Google Sheets or Excel it is very simple to quickly identify unique values in a list using a formula.
Necessary Items:
Microsoft Excel or Google Sheets
Your data open in Microsoft Excel or Google Sheets
Steps
Open your dataOpen your data in either a Google Sheets or Microsoft Excel spreadsheet.
You may have many columns of data. Before continuing identify the column where you want to identify unique values. For example, if this is a list of contacts for import into Mailchimp then the email address field is likely to be the field you want to use.
Create a new column in your spreadsheet. Call the column “Duplicate”.
Enter the formula
=COUNTIF(A:A, A2)>1into the first cell in your “Duplicate” column (i.e. immediately under the header titled “Duplicate”). This formula assumes that column A of your spreadsheet contains the data that you want to analyze.
Then, copy the formula entered into all rows that contain data i.e. drag the cell containing the formula down the column. The “Duplicate” column will now show “FALSE” where values are unique.
Create a filter on the header rows of your columns. By filtering the “Duplicate” column to show only FALSE values will show uniques records in your list.
Leave a Reply Cancel reply