I have a spreadsheet with over 2000 rows of data I would like to quickly
isolate those with duplicate names or phone numbers. Can I do this in Filter?
I know I can eliminate duplicates but I want to find them.

Bill

If you just want to identify them and we'll say they are in A1:A2000 then in
a spare column alongside enter

=COUNTIF($A$1:$A$2000,A1)

and copy down

This will give you the number of times they appear in the list and you can
autofilter or sort on that

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(E-Mail Removed)HIS


"Bill in Wichita" <Bill in (E-Mail Removed)> wrote in
message news:F5ED130B-6693-408B-97D7-(E-Mail Removed)...
>I have a spreadsheet with over 2000 rows of data I would like to quickly
> isolate those with duplicate names or phone numbers. Can I do this in
> Filter?
> I know I can eliminate duplicates but I want to find them.

Hi Bill,

try to use a pivot table

hth
regards from Brazil
Marcelo

"Bill in Wichita" escreveu:

> I have a spreadsheet with over 2000 rows of data I would like to quickly
> isolate those with duplicate names or phone numbers. Can I do this in Filter?
> I know I can eliminate duplicates but I want to find them.

Actually, I had the same problem once.

First I did sort by this column. Suppose your column is A. on one empty
column, for example E, in cell E2 put formula =If(A1=A2, 1, 0), and extend to
all of the column E. Then all of the duplicated records will be found. sum of
column E, you will know how many duplicates you've got.

Hope it can send you help!

Viesta
Shanghai, CN

"Bill in Wichita" wrote:

> I have a spreadsheet with over 2000 rows of data I would like to quickly
> isolate those with duplicate names or phone numbers. Can I do this in Filter?
> I know I can eliminate duplicates but I want to find them.

This will give comparision result to one particular cell. What about other
duplicate values.

Thank You
Vij

"ViestaWu" wrote:

> Actually, I had the same problem once.
>
> First I did sort by this column. Suppose your column is A. on one empty
> column, for example E, in cell E2 put formula =If(A1=A2, 1, 0), and extend to
> all of the column E. Then all of the duplicated records will be found. sum of
> column E, you will know how many duplicates you've got.
>
> Hope it can send you help!
>
> Viesta
> Shanghai, CN
>
> "Bill in Wichita" wrote:
>
> > I have a spreadsheet with over 2000 rows of data I would like to quickly
> > isolate those with duplicate names or phone numbers. Can I do this in Filter?
> > I know I can eliminate duplicates but I want to find them.

But of course! Thanks - you saved me! Forget the pivotal tables and the
formulas. This is much easier.
Peggy

"ViestaWu" wrote:

> Actually, I had the same problem once.
>
> First I did sort by this column. Suppose your column is A. on one empty
> column, for example E, in cell E2 put formula =If(A1=A2, 1, 0), and extend to
> all of the column E. Then all of the duplicated records will be found. sum of
> column E, you will know how many duplicates you've got.
>
> Hope it can send you help!
>
> Viesta
> Shanghai, CN
>
> "Bill in Wichita" wrote:
>
> > I have a spreadsheet with over 2000 rows of data I would like to quickly
> > isolate those with duplicate names or phone numbers. Can I do this in Filter?
> > I know I can eliminate duplicates but I want to find them.

"Bill in Wichita" wrote:

> I have a spreadsheet with over 2000 rows of data I would like to quickly
> isolate those with duplicate names or phone numbers. Can I do this in Filter?
> I know I can eliminate duplicates but I want to find them.

Bewdy Nick, Simple and works a charm

"Nick Hodge" wrote:

> Bill
>
> If you just want to identify them and we'll say they are in A1:A2000 then in
> a spare column alongside enter
>
> =COUNTIF($A$1:$A$2000,A1)
>
> and copy down
>
> This will give you the number of times they appear in the list and you can
> autofilter or sort on that
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> www.nickhodge.co.uk
> (E-Mail Removed)HIS
>
>
> "Bill in Wichita" <Bill in (E-Mail Removed)> wrote in
> message news:F5ED130B-6693-408B-97D7-(E-Mail Removed)...
> >I have a spreadsheet with over 2000 rows of data I would like to quickly
> > isolate those with duplicate names or phone numbers. Can I do this in
> > Filter?
> > I know I can eliminate duplicates but I want to find them.
>
>
>