deduplicate data in excel

[Guest]

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

 

[Dave Peterson]

You may want to read some of Chip Pearson's techniques for dealing with
duplicates:
http://www.cpearson.com/excel/duplicat.htm

 

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Dave Peterson]

What formula did you use?

Where's your data?

I've never had trouble with the formulas.

 

[Guest]

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

 

[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.

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

 

[Guest]

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

Julie Melbourne

 

[Dave Peterson]

Glad you got it working.

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

Julie Melbourne