MrExcel.com - Your One Stop for Excel Tips & Solutions

Duplicate cells

Posted by John on December 29, 2001 7:53 PM

Real novice here!! Can anyone help, we imported a list of phone numbers and need to identify the duplicates. If we could indicate the duplicate in a cell we would sort and collect them all in one section then manually delete the dups. Thanks in advance. This msg board is great.

Posted by Tom Urtis on December 30, 2001 2:29 AM

It sounds like all you want to look at are unique data entries, in your case telephone numbers. There are several ways to do that...here are 2 painless options:

Highlight (select) your range of numbers, then click on Data > Filter > Advance Filter, check Unique records only, and hit OK. You can then copy that filtered list of visible unique entries and paste it anywhere else in your workbook to create a new unique list. If you want to create a new unique list on the same worksheet as your original data, then in the Advance Filter dialog box, select "Copy to another location" and in the Copy to field, enter the first cell of that sheet you want to have the new list start in.

Alternatively, if your list begins in A2, then in B2 you could enter the formula
=IF(COUNTIF($A$1:A1,A2),"",A2)
and copy down as needed.
Next, highlight column B, click Edit > Copy, then highlight column A, click Edit > Paste special > Values, hit the Esc key, and highlight column B once more and hit the Delete key. You will be left in column A with unique telephone numbers.

Hope this helps.

Tom Urtis

Posted by Richard Winfield on December 31, 2001 8:07 AM

Heres another even more painless option ;)

http://www.asap-utilities.com/

and download the free Excel add-in "ASAP Utilities" This will add the ASAP utilities to Excel and under the "RANGE" submenu there are choices to remove or hide all duplicate entries within a selection or sheet. Simply select the range and run the macro.

A side note, this is one of the best add-in packages for Excel and will save you a bunch of time with many other things as well. Something over 200 different macros setup on a menu.

Happy Holidays to all.