deduplicate data in excel

J

Julie Melbourne

How do I deduplicate data in Excel. I have a large database of names and
addresses which I want to check to duplication
 
J

Julie Melbourne

Thanks Dave
I have looked at this site, but cannot get the formula to work?
Must be doing something wrong. Have you tried it?
Do you have a working spreadsheet wtih this formula?

Thanks for you help
Julie Melbourne
 
B

Bob Phillips

Look at Data>Filter>Advanced filter, it has a unique option.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

What formula did you use?

Where's your data?

I've never had trouble with the formulas.
 
J

Julie Melbourne

Dave
I tried the following formula in a test worksheet using one column named
Range with same data as displayed on the website

=IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")

Then I pressed CTRL+Shift+Enter
I get the following result
#NAME?

Any ideas what I am doing wrong?
Much appreciate your help
 
D

Dave Peterson

First, that's not the formula you want to use. But if you just wanted to check
to see if all the entries were unique, you'd change range1 to A1:A100 (or some
other range).

I think the formula you want (if your data is in A1:Axx) is this:
=if(countif($a$1:a1,a1)=1,"First Occurrence","Duplicate")

Then the first occurrence is marked differently.

If you're looking for just any old duplicate.
=if(countif(a:a,a1)=1,"unique","duplicate")

And drag done the column.

Julie said:
Dave
I tried the following formula in a test worksheet using one column named
Range with same data as displayed on the website

=IF(MAX(COUNTIF(Range1,Range1))>1,"Duplicates","No Duplicates")

Then I pressed CTRL+Shift+Enter
I get the following result
#NAME?

Any ideas what I am doing wrong?
Much appreciate your help
Click to expand...
 
J

Julie Melbourne

Dave
Thanks so very much. It works a beauty!
Much appreciated

Julie Melbourne
 
Top