How can I find duplicates in one column of an exel spreadsheet? in Excel Worksheet Functions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              Bill in Wichita 7/12/2006 1:26 PM PST   Question   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.     Was this post helpful to you?         Reply |   Top           Marcelo 7/12/2006 1:33 PM PST   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.     Was this post helpful to you?         Reply |   Top           Nick Hodge 7/12/2006 1:36 PM PST   Answer   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 nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Bill in Wichita" <Bill in Wichita@discussions.microsoft.com> wrote in message news:F5ED130B-6693-408B-97D7-303201660F77@microsoft.com... >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.     Was this post helpful to you?         Reply |   Top           gregtops 9/25/2009 6:09 AM PST   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 > nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS > > > "Bill in Wichita" <Bill in Wichita@discussions.microsoft.com> wrote in > message news:F5ED130B-6693-408B-97D7-303201660F77@microsoft.com... > >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. > > >     Was this post helpful to you?         Reply |   Top           ViestaWu 7/12/2006 11:38 PM PST   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.     Was this post helpful to you?         Reply |   Top           Vij 10/27/2006 9:39 AM PST   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.     Was this post helpful to you?         Reply |   Top           Peggy 6/2/2008 3:45 PM PST   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.     Was this post helpful to you?         Reply |   Top           GRM 7/14/2009 2:10 AM PST   "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.     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies