I've tried numerous ways of entering the data but it still won't work. I'd
have thought this would have been a simple exercise but so far it's proving a
real nightmare. As you suggested in Column A I have a long list of phone
numbers. I want the duplicate numbers to appear in column B. I dont want
the duplicate number to appear more than once though.

Thanks

"Gazza" wrote:

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!!!