Discussion on:

How to find duplicates in Excel

This would require you to sort the data. If that is an option, this might be easier for some. I insert a column to the right of the one with potentially duplicate data. (Say potential duplicates are in column A, I insert a blank column at B.)

First row is usually headings that will not match the data itself. (I'll change the heading for A if I have to.) Let's assume for this that the data starts in row 2 with headings in row 1.

I put a formula in cell B2 as follows: =if((a2=a1),"Dup","-")

I get 'Dup' if there is a duplicate, '-' otherwise.

I hope this helps.

Well done. I will definitely be using this.

Mattohare uses the same method as I do. However, I use 1 if a dup is found and blank if no dupe. Because in most cases I need to manually delete the duplicates after inspecting the 2 rows of data, I sum the column with the formula in it. This allows me to see how many more I have to go.

But there were times when the autofill didn't go all the way down. I had to know that the formula was actually there.

Just noted my approach was already described below.

I do something similiar except in my formula, I put =if((A2=A1),0,1)

I then drag the formula down, copy it then paste special and select 'values'.

I then do an ascending sort on my new values. All the "0" are now at the top of the page, easier to select and I do a Ctrl-X to cut them out then past them in a new sheet labeled duplicates just in case there are values in some of the other columns that are needed.

I've actually written a macro that does all this for me since I frequently have to check for email/phone number duplicates. It also removes all formatting from phone numbers as well and applies phone number formatting to the cells with phone and fax numbers.

I think I may do an article on the Macro during the holidays and post it. It is a very useful tool for scrubbing data for mailing lists.

Hi,
It's not commun to use such function with excel I prefer to use MSACCESS to find duplication it's much better.

I hope this help
Rgds
Naoufel

A lot of offices will not install it for employees, but they still need to get the work done.

In my case, I often have data scrubbing that must happen in Excel, because some data will not go into a database field until it's been through its own scrubbing.

You can easily create a Pivot table using the row where potential duplicates are as the Row Labels and "Count Of.." as a summation value. The result is the number of occurances of each value in the selected column. If you want, you can then cut and paste special (values only) the Pivot table which will allow you to sort from high to low or vice versa.

Sort on most unique field you have.
Place focus in the first cell of this field.
Select Conditional Format-->New Rule.
From Select a Rule Type, select format unique or duplicate values.
Make sure duplicate is in the drop down box
Click on the format button and choose a color.

Now everything easily stands out.

With a bit of care, I've gotten pretty good results with Excel 2007's built-in Remove Duplicates command. Data tab > Data Tools > Remove Duplicates.

I have a similar problem.

I have two columns.
I want two columns to become one but not in the same way.

If columns look like:
A B
Cat Dog
Dog Cat

I want C to look like:

Cat
Dog
Dog
Cat

Then I want to find the duplicates in one column

Ugh! Too much work. I prefer to let the computer do the work for me. I sort first then use custom macros:

Sub NextAlike()
' This macro moves the active cell down until it finds a duplicate.
a = 0
Do Until a = 1
Item = ActiveCell
ActiveCell.Offset(1, 0).Range("A1").Select
NextItem = ActiveCell
If NextItem = Item Then
ActiveCell.Offset(-1, 0).Range("A1").Select
a = 1
End If
Loop
End Sub

If I want to delete duplicates:

Sub DeleteDups()
' Deletes entire rows that have duplicate data in the current column.
' Safety Recommendation: Do not setup a toolbar button for this macro.
DialogStyle = vbYesNo + vbCritical + vbDefaultButton2
Title = "Deleting Duplicates"
Msg = "Are you sure you want to delete entire rows?"
deleteDupRows = MsgBox(Msg, DialogStyle, Title)

If ActiveCell.Value "" And deleteDupRows = vbYes Then
Do While ActiveCell.Value ""
noDups = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
If ActiveCell.Value = noDups Then
DelStartRow = ActiveCell.Row
Do While ActiveCell.Value = noDups
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
DelEndRow = ActiveCell.Row - 1
Range(Cells(DelStartRow, 1), Cells(DelEndRow, 1)).EntireRow.Delete
GoBack = (DelEndRow - DelStartRow + 1) * -1
ActiveCell.Offset(GoBack, 0).Range("A1").Select
End If
Loop
ElseIf deleteDupRows = vbYes Then
Message = "You must start from a cell containing data."
X = MsgBox(Message, vbOKOnly, "Empty Cell")
End If
End Sub

I recently needed to make sure that I had three codes in a row of data with no duplicates, so I used the following formula (a1, a2, and a3 being the codes):

=IF(SUM(COUNTIF(C2:H2,"a1")=1, COUNTIF(C2:H2,"a2")=1, COUNTIF(C2:H2,"a3")=1 SUM(COUNTIF(C2:H2,"a1"), COUNTIF(C2:H2,"a2"), COUNTIF(C2:H2,"a3")), "err", SUM(COUNTIF(C2:H2,"a1"), COUNTIF(C2:H2,"a2"), COUNTIF(C2:H2,"a3")))

This gives me a count (up to 3) of unique codes and an "err" if a duplicate code is found.
It might not be very pretty or elegant, but it works.

I have an excel worksheet (mac). Within the worksheet I want to find matching entries in columns F and G which i know are there but the spreadsheet is large. I need all other data in the worksheet to maintain their row integrity when the match is made eg. F3 data is same now as G3 data but all other cells have kept their original data when the match was made.... not explaining this too well...

Hello all,
can you please guide me an excel problem where i actually struck. actually i have x number of persons with y available dates. each person gave me their available dates to set up an meeting. now i want to display one specified date where all persons are available and arrange a meeting. how can i do that in excel. please help me to solve the problem.

I like the first method of filtering out the duplicates, but just to make sure, does this keep ONE of the duplicates?? (As opposed to deleting them both). That would mean I'd have to do a V-Lookup between the filtered and the unfiltered list? I hope not...

Thanks for the helpful hints. This looks like a complicated yet worthwhile solution to my current solution-- the incredibly ineffective "CTRL + F" search-and-delete.

In Accounting it is easier to find a $10,000 error than a 10 cent error. Thanks for the taking the time to share some tips with us all.

I used to find duplicated manually. what a waste of time! Thanks for this tutorial! my shed plans review

Or use the build-in feature; Remove Duplicates. You can find this feature on the Data tab under Data Tools.

You've mentioned a way to remove duplicates from excel. But the article looks little old and the screenshots may not apply to the latest editions of Excel 2007/2010. Here is another article which explains the same in detail.
http://www.techlikes.com/2011/08/23/remove-duplicates-in-excel-20072010.html

There is a much easier way to do this, use this tool to help you with fuzzy logic inexact matching: http://www.fuzzy-logic.com

Excel add-in ASAP has a function to detect duplicates in a column - plus many other useful functions. I've used it for years. Go to asap-utilities.com. It's free.

Thanks for sharing as i was not knowing about it.