Home |
Search |
Today's Posts |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Tom,
The first part of your solution worked well, with "Duplicate" appearing in column B. The second part unfortunately does not work. I don't understand the bit about "hold down Control, Shift and Enter when entering the formula"..... When I do this the "insert cells" dialogue box appears. Entering the formula normally and pressing enter or clicking the green "tick" in the formula bar simply re-creates the first phone number. Copying and pasting (paste special, formula) into the rest of the cells (C2:C99) puts a small square box in them and nothing else happens. Sorry to be a pain in the butt Tom but this is driving me nuts. "Tom" wrote: You can do this but it won't be just one easy formula. Assuming you have your phone numbers in column A, in cell B1 insert and copy down this formula: =IF(COUNTIF($A$1:$A$11,A1)1,"Duplicate","") You need that formula because it shows the duplicate phone numbers. Then in cell C1, insert and copy down this formula: =IF(ISERROR(INDEX($A$1:$A$9,SMALL(IF(COUNTIF($A$1: $A$9,A1)1,ROW($A$1:$A$9)),ROW(1:1))))," ",INDEX($A$1:$A$9,SMALL(IF(COUNTIF($A$1:$A$9,A1)1 ,ROW($A$1:$A$9)),ROW(1:1)))) Hold down Control, Shift, and Enter when entering the above formula. Hitting Enter like normal won't do it. Then copy it down as far as the phone numbers go. This makes a list of all the phone numbers that appear more than once. In column D, copy the list from column C. Copy, Paste Special, Values only. Organize column D by ascending value and filter by unique values, Just like Stefi said to do. Now you have a list of unique phone numbers that appear more than once in column A. It's not a quick and easy process but I couldn't think of anything easier!!! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Remove duplicates | Excel Worksheet Functions | |||
Remove Duplicates | Excel Worksheet Functions | |||
remove duplicates | Excel Discussion (Misc queries) | |||
Remove Duplicates | New Users to Excel | |||
How to remove duplicates? | Excel Discussion (Misc queries) |