I have a several-thousand line spreadsheet that includes a column of phone numbers. I need to confirm that none of the phone numbers are duplicates. Is there any alternative to entering each and every phone number manually in the Find search box? Is there any way to get Find to pick up the value of the currently selected cell as the search text?
Insert a temporary column next to the phone numbers. Enter =COUNTIF(Col:Col, Cell) > 1. Auto-fill everything. Sort for TRUE.
Link to a Microsoft article showing a solution to this in depth with pictures and etc.
Yup. Let’s say you have 10 phone numbers going down Column A, starting with Row 1. Enter the following into B2:
=countif($a$2:$a$11, a2)
Drag that down column B. If there are any non 1s, then you have duplicates.
I usually sort on the field I’m checking for Duplicates. Then look through the list. The dups are usually easy to spot.
There are times the dups can’t be found with code. A Street Address. 12 Elm Street and 12 Elm St You’ll spot it easily in a sorted list.
The Microsoft article sounds interesting. I need to look at that myself.
A couple of alternatives:
Use a pivot table, categoried by number, sorted by descending count.
Use conditional formatting, highlighting any cell which is equal to the cell above
Or my favorite options when you’re talking about a dataset being stored in excel that is thousands of rows long:
Store it in a relational database where things like SELECT DISTINCT are trivial.
I understand that helps the OP not at all, but I do think it serves as a good example of a situation in which you may be better off using something other than spreadsheet technology. You can even still use Excel as the front end to a database if that was your inclination.
In Excel 2010, there’s a handy-dandy command that will highlight duplicates using conditional formatting.
I haven’t attempted it, but if you don’t want to just scroll down and look for colors, I suspect you could then create a function that would enable you to automatically count the number of cells that match that formatting.
I always use something like,
=IF(A2=A1,"*","")
and drag copy it to the bottom of an empty column. Any row that has an * is a duplicate.
ETA: To state the obvious, first sort the list by ph#.
Oh right, sorting by phone# would put any duplicates next to each other. I’ll check out the suggestions given (I’m not experienced with functions so I’ll have to figure out how it works). Thanks all!