Email:      Pass:    Pass?

Free Excel Forum

Find Duplicates Formula

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have two columns of email addresses and I want to make sure that between these two columns there are no duplicates. Can the excel gurus please help.


I attached a test doc that I am using - I can't seem to get it to work.

Similar Excel Video Tutorials

Helpful Excel Macros

Send Emails through Outlook using Email Addresses from Excel and text from Word
- This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
Email Current Workbook & or Other Attachments
- This macro will send the current workbook in an email through Microsoft Outlook. The Macro allows you to send the most
Display The Actual Link / Email Address From Links in Excel - UDF
- Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt
Delete Duplicate Rows
- This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete

Similar Topics

Hello all! What I am trying a accomplish is to compare two columns of email addresses to find the duplicates. Then, I need to be able to sort or somehow organize the identified duplicates. There are 2,369 addresses in column A, and 5,360 in column B.

I have tried a number of formulas found online, but nothing seems to work. I'm a novice at Excel functions and would greatly appreciate help with this.

Thank you!

Hi Everyone!

I need some help modifying a formula. In the attached spreadsheet, have 1 column with email addresses separated by a semi-colin. Sometimes, the email addresses are the same. With some help from some great online friends, a formula was created to look at the 2 email addresses in the same cell, decide if they are duplicates, and return only one email address (without the semi-colin).

This formula has been working great until recently as the cells can now have 3 email addresses in the same cell. (don't worry there won't be more that 3). Is there a way to modify the formula to look and see if the second and third email addresses are duplicates?

Hi Everyone,

I need your help! I have a spreadsheet that contains 1-3 email addresses in a single cell separated by a semi-colin. Some of the email addresses are duplicated. Is there a formula/function that can look at the email addresses in the cell, and return the email addresses removing the duplicates?

I have attached a sample document with my desired outcome.

Thanks in advance for the help,

Hello. This is probably a pretty basic question.

I'm trying to compare two columns of email addresses to check for duplicates and then tally those duplicates. It's only duplicates that I'm interested in.

I've tried a nested IF function that I got from a Microsoft article. Here it is:
=IF(ISERROR(MATCH(A12,$C$2:$C$377,0)),"no match","match")

I think it's wrong because the results make no sense.

The columns are of unequal length, with about three times the number of addresses in one column compared to the other. I want to see how many of the addresses in the shorter column are duplicated in the longer one.

I've been fiddling with it for a while now and have managed to confuse myself, so I'd be very grateful if someone could either correct my function or perhaps suggest an alternative method to achieve the same result.


Hey everyone,

I've been trying to get my head around this since long but cannot quite figure it how to do it.

I have an Excel sheet with a single column that lists about a 1500 email addresses. There are a lot of duplicates, and I want to count them.

The output I need to get should look like this:

EmailAddress -------------- # of Occurences
Email Add 1 ---------------------------- 4
Email Add 2 ---------------------------- 4
Email Add 3 ---------------------------- 4
Email Add 4 ---------------------------- 3
Email Add 5 ---------------------------- 3
Email Add 6 ---------------------------- 3
Email Add 7 ---------------------------- 2
Email Add 8 ---------------------------- 2
Email Add 9 ---------------------------- 2
Email Add 10 -------------------------- 2
Email Add 11 ------------------------- 1
Email Add 12 -------------------------- 1

So basically, counting the number of times an email address is in the column and arranging them in descending order.

I was able to highlight the duplicates using Conditional Formatting and CountIf, but I cannot figure out how to get the above output.

Any ideas on how to do this are appreciated

How do you find unspecific duplicates? I wanted to sort a column of email addresses and go through and find the ones that were in there twice. How would I go about doing that? I know the =countif function to see if there are more than one of the specific email addresses, but I'd like it to show me the ones that are repeats. Anyone that knows that function? or has a roundabout way of achieving what I need? Thank you!

hello, New to the forums. Wow what a great wealth of knowledge here. Glad I found it.

Anyway my question is. I work with two reports everyday and they both share a column that have similar but one holds a little more data.

For right now I've been cutting and pasting one column and inserting it into the other report. Sort in ascending order and eye it up to find duplicates.

I've tried to use some formulas that I've found on here but they don't seem to work or I'm not using them right.

So right now I have columns H & I that hold the same data but column I has 20-30 cells that are not duplicates. So both columns are not the same size. I am trying to find a formula that
Comapares column I with column H and identifies the duplicates.

OR Even better a formula that combines column H & I and deletes the duplicates.

Is this possible or is there an easier way to get around this?


I would like to know if anyone has a formula to build email addresses in Excel. I have a spreadsheet with 3 columns (company. contact, domain) and need to make 2 additional columns (highlighted) that contain 2 email addresses. See pic below.

Any thoughts? Thanks!


Have a quick questions. i have columns A and B. in column A i have first name in column B i have email addresses. i already removed Duplicates using the automatic function in the Data menu.

however, i still have some duplicates because the email address may appear twice where someone has 2 different name types, like Andy and Andrew. So for example, i have have this email twice in column B: and because it s listed in the firstname column once as Andy and another time as ANdrew. I would like to put in a formula that deletes one of the rows, i don't want to send someone an email twice. i really don't care if it picks Andy or Andrew, i just don't want the email to appear twice. Any ideas? i started to think it would be done w the VLOOKUP function but i just cannot figure it out. if anyone can help let me know. Thanks!!

In column A, I have 500 email addresses.
In column B, I have 35 email addresses.

I want to see if ANY of the 35 email addresses in column B exist in Column A and if they do, remove them from column A.

A while back, someone suggested:
=Isnumber(Match(A1,X:X,0)) will return TRUE if value in A1 matches anything in column X... then you can delete the ones where TRUE shows.

But for some reason, EVERYTHING is returning as false. Can someone please help me a) by giving me a new formula to try? or b) help me troubleshoot what's wrong with the above formula where I'm only getting False results?

P.S. I already know that the 35 email addresses in column B are duplicated in the larger list of column A with 500 email addresses. I just don't want to have to go and delete manually by using the "find" feature. Thanks in advance!

Hi there,

Having a bit of trouble with isolating duplicates - the conditional formatting approach doesn't seem to work.

In short - I have 70,000+ records over 8 columns. I have removed duplicates across all 8 columns, but I now need to isolate duplicates that match 7/8 columns.

I can remove these by unticking one of the columns using the remove duplicates function, but this won't let me isolate them.

The duplicates (98 of them) will contain two versions, with different numbers in the column that need to be checked manually for the correct one - subsequently removing the erroneous version.

If anyone has any ideas on how best to approach this, I'd be eternally grateful!

Many thanks, Tom

I have 2 columns of email addresses (from two sources)
I need to compare the two to eliminate duplicate email addresses.
What formula can I use to search Column A and if it contains the same email address that is in Column B to bring back "duplicate"

I have about 1200 rows of email addresses. 2 columns (column A from group 1 and column B from group 2) Basically trying to consolidate into one list with no duplicates.


Hi all,

First time poster on this site, although I have visited a few times

I have an excel spreadsheet that has 4 worksheets.

Duplicates: is where I would like duplicates to be copied
Triage: shows work outstanding in a "triage" area
SS Emails & CS Emails: have data copied from 2 Outlook mailboxes

What I would like to do is find duplicates of the subject / summary columns, so anything that is in the triage area that exists in either of the x2 mailboxes is copied into the Duplicates worksheet - Ideally what I would like to do is copy the whole row of the matching cells (from either of the mailbox worksheets) so I can see who dealt with the email - As it has their name in the "categories" field of both outlook worksheets (SS Emails & CS Emails).

So I've been banging my head at this for a few days now, and feel pretty burnt out. If this was possible, it would take a lot if the pain out of doing this task manually, or at least remove a lot of the effort.

Hope this makes some sense.. It's been a long day

Any help would be appreciated! - I have attached the spreadsheet and omitted sensitive information such as email addresses, names of people who have sent emails and subject / summary names.


I have a macro that deletes a row if there are duplicates, but it is just duplicates based on one particular columns. So, how do I delete the duplicate rows if two columns are the same in more than one row. And there are several columns but the other columns in the spreadsheet are unique. These two columns can contain duplicates. I attached a copy of a test spreadsheet. Thanks in advance.

Hi there,

I have a list of about 5,000 words I need to check for duplicates in Excel. At least 5% of them are duplicates. And of course, I need to keep the words correlated with the PPC price, which makes up the other of 2 columns (see attached screenshot).

Is there any very simple way to do this in Excel? All the tutorials I have seen online involve formulas and mathematics and look like hieroglyphics to me. I can't imagine that a program this powerful cannot easily find duplicates.




Basically I have a list of 16,000 email addresses. I then have a separate list of 5,000, all of which don't work. What I need to do is delete any email address in the large list which appears in the small list. Deleting duplicates doesn't work. I can't think of a way of doing it other than going through and individually deleting email address.

Help please.

Hi, after reading through many posts and web articles tutorials etc I cannot seem to find my answer.

My issue is that i have 2 columns, A & B

B is a much shorter list than A

I am trying to find everything From B that appears in A. Perferably by hilighting. However, there are duplicates within A, so highlight duplicates will not work. I am using Office 2007.....

Just to reiterate an example:

Column a has: 1,2,3,4,4,3,2,2,2,1
column B has: 2,3

I want to just highlight the 2's and 3's in A without highlighting the duplicates 4 and 1......

any advice would be appreciated but remember that i am working with about 30,000 in A and 1200 in B...


I have two lists. Each have 3 columns. First Name, Last Name, Email Address

I can take the one workbook list, and add into the other to have one large list.

1. I would like to highlight all the rows that have identical data.
I'm using Excel 2007. I found a way to Highlight duplicates, but it only does it for one column (works great when highlingting duplicate emails). However, sometimes there are no email addresses listed, so I want to see the first & last name combo duplicates.

Example: (Both would count as a duplicate)
Ricky Henderson
Ricky Henderson
John Smith (Blank)
John Smith (Blank)

2. I would like to then count how many duplicates there are. (Above example being 2 duplicates)

3. I then would like to have an option to delete all the duplicates.

This would allow me to see who is duplicated, know how many were duplicated, and delete the duplications.

Ill take an add-in, macro forumula, any suggestions you have. Thanks for the help!

Hi All,

I am trying to write a piece of code that helps me to remove duplicates from my data and cleans up the results.
The code should find duplicates in the same colum and copy the data in the rows of these duplicates to one row (the one of the first duplicate). Then it should delete the now empty duplicates and move on to the next set of duplicates.
See the attached file for a before and after.

I hope I make myself clear, any help is greatly appreciated!


Kindly find attached excel sheet which containing duplicate values. I tried to find out the duplicates using conditional formatting and "if countif" formula as you see in the attached excel file. however some of the values are in duplicates,shows as it is not duplicate i,e in B51 & B59 are duplicates but the result is as not duplicate. I suspect more such values might be there in the list.

Thanks & appreciate for your assistance as I have noticed excel forum gurus/contributers are very quick responders.



I have a list of email addresses which I would like to find only the
new email addresses within the list. Inside of my Excel worksheet I
have a row of e-mail addresses. Some of the names are duplicated which
tells me they already exist somewhere in the row. I wish to display
only the new names. Can you tell me how this can be done in Excel?

Thanks in advance for your assistance,

I'm looking for a formula to remove duplicates that may be formatted across 25 columns.
B1, C1, D1...Z1

The data could span across all 25 columns or just 1 or 2. What I am looking for is a formula I can start say at C25 & drag down to C50 that will ignore duplicates of B1, C1, D1...Z1 & yet not leave any blank rows between that data.

Columns B1, C1, D1, E1, F1, G1 contain Bob, Bob, Sam, Sam, Bob, Ted
Beginning at C25-28 would contain Bob, Sam, Ted

Does this make sense? p.s. I'm using Excel 2003.


I am trying to find duplicates between two columns. However, one of the columns only contains the values of the first, so they aren't exact duplicates. Example:
Column 1: 213, 445, 565
Column 2: 1-445, 89-565, 445-55

What formula can I use so that excel returns a value if it finds '213' in the second column? I have been using this formula befo


However, this formula only works for matches, but now I have data with dashes and extra numbers.


I am have a great deal of trouble. I have to compare over 10000 numbers between two columns. I cannot find a formula that will compare the two rows instead of comparing the information in just that one row. My point is say i have 10,000 numbers in one column and 10,000 in another column. I need to compare the two columns and make sure there are no duplicates between them. Can you help?? I have beensearching through threads and i cant find anything that works!


Col:A Col:B
1 ---------7
2 ---------8
3 ---------9
4 --------12
5 --------2 *
2 --------3 *
3 --------15
4 --------1 *
5 ---------3 *
6 ---------13

how can i find these duplicates without looking through each one?

I have merged about 20 spreadsheets into one large sheet for mail-merging purposes. Some of the sheets I have merged have addresses and e-mails whereas others only have addresses. If column D contains all the addresses and column J contains the email addresses, is there away to run a macro duplicate formula that will delete the address without the email address and keep the address with the email address?

Does this make any sense?

Thank you!