deduplicate data in excel

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

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

Julie Melbourne wrote:
>
> 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

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

"Dave Peterson" wrote:

> You may want to read some of Chip Pearson's techniques for dealing with
> duplicates:
> http://www.cpearson.com/excel/duplicat.htm
>
> Julie Melbourne wrote:
> >
> > 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
>

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

--

HTH

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


"Julie Melbourne" <Julie [email protected]> wrote in
message news:[email protected]...
> How do I deduplicate data in Excel. I have a large database of names and
> addresses which I want to check to duplication

What formula did you use?

Where's your data?

I've never had trouble with the formulas.

Julie Melbourne wrote:
>
> 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
>
> "Dave Peterson" wrote:
>
> > You may want to read some of Chip Pearson's techniques for dealing with
> > duplicates:
> > http://www.cpearson.com/excel/duplicat.htm
> >
> > Julie Melbourne wrote:
> > >
> > > 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
> >

--

Dave Peterson

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" wrote:

> What formula did you use?
>
> Where's your data?
>
> I've never had trouble with the formulas.
>
> Julie Melbourne wrote:
> >
> > 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
> >
> > "Dave Peterson" wrote:
> >
> > > You may want to read some of Chip Pearson's techniques for dealing with
> > > duplicates:
> > > http://www.cpearson.com/excel/duplicat.htm
> > >
> > > Julie Melbourne wrote:
> > > >
> > > > 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
> > >
>
> --
>
> 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 Melbourne wrote:
>
> 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" wrote:
>
> > What formula did you use?
> >
> > Where's your data?
> >
> > I've never had trouble with the formulas.
> >
> > Julie Melbourne wrote:
> > >
> > > 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
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You may want to read some of Chip Pearson's techniques for dealing with
> > > > duplicates:
> > > > http://www.cpearson.com/excel/duplicat.htm
> > > >
> > > > Julie Melbourne wrote:
> > > > >
> > > > > 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
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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

Julie Melbourne

"Dave Peterson" wrote:

> 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 Melbourne wrote:
> >
> > 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" wrote:
> >
> > > What formula did you use?
> > >
> > > Where's your data?
> > >
> > > I've never had trouble with the formulas.
> > >
> > > Julie Melbourne wrote:
> > > >
> > > > 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
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > You may want to read some of Chip Pearson's techniques for dealing with
> > > > > duplicates:
> > > > > http://www.cpearson.com/excel/duplicat.htm
> > > > >
> > > > > Julie Melbourne wrote:
> > > > > >
> > > > > > 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
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Glad you got it working.

Julie Melbourne wrote:
>
> Dave
> Thanks so very much. It works a beauty!
> Much appreciated
>
> Julie Melbourne
>
> "Dave Peterson" wrote:
>
> > 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 Melbourne wrote:
> > >
> > > 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" wrote:
> > >
> > > > What formula did you use?
> > > >
> > > > Where's your data?
> > > >
> > > > I've never had trouble with the formulas.
> > > >
> > > > Julie Melbourne wrote:
> > > > >
> > > > > 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
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > You may want to read some of Chip Pearson's techniques for dealing with
> > > > > > duplicates:
> > > > > > http://www.cpearson.com/excel/duplicat.htm
> > > > > >
> > > > > > Julie Melbourne wrote:
> > > > > > >
> > > > > > > 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
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson