CODEWITHSUNDEEP
excel
Milan
Milan

Reputation: 3325

How to single out unique records in the whole spreadsheet?

In EXCEL 2010 I have 2 columns which contain email addresses. I need to find those which are present in the entire spreadsheet (in both columns) only once and single them out, meaning if one record is present in column 1 and 2 then delete both records leaving only those which never appeared more than once. Any ideas ?

Upvotes: 0

Views: 149

Answers (2)

Marc
Marc

Reputation: 11613

PREPROCESSING

Put them all in the same column. Use LOWER() if necessary to make sure they're all the same case. Use TRIM() if necessary to remove extra spaces. Sort them (not necessary with COUNTIF(), but will make spot-checking easy).

MAIN PROCESSING

Let's say your email addresses are in column A, from A2:A1000.

In cell B2, enter this formula:

COUNTIF($A$2:$A$1000,A2)

This will produce the count of each each time the email address in A2 appears in the column. Copy that down column B.

Use Excel's Filter feature to filter your list for those that have exactly 1 in column B.

Upvotes: 3

user2183981
user2183981

Reputation: 1

=IF(COUNTIF($A:$B;A1)=1;A1;"not unique")

If your e-mail addresses are in column 1 and 2, try this formula in column 3

Upvotes: -1

Related Questions