How do I remove duplicates

[Gazza]

I have a spreadsheet showing telephone numbers of people who have called the
fire brigade. I have used the "=countif" command to highlight those numbers
which appear more than once, (they appear in red). I need to be able to move
the duplicate numbers to a separate column but only show the duplicate number
once in that column. Any ideas?

 

[Stefi]

Select first (header) cell of Phone Number column!
Data>Filter>Advanced filter

Check Copy to another location
Leave List range as is (something like $A$1:$A$12)
Move to Copy to field
Click on cell you want to place the filtered list to
Check unique records only
Click on OK


--
Regards!
Stefi



„Gazza†ezt írta:

 

[Gazza]

Sorry Stefi but your solution didn't work. All I got was the same details of
all the phone numbers as each number was unique. I need it to show me only
the numbers which are duplicated but show the duplicate number only once.

 

[Tom]

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

 

[Gazza]

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.

 

[Gazza]

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

 

[Tom]

The formula is different to enter than a normal formula because it is an
array formula.

Paste the formula into the cell. Then click in the formula bar next to the
'fx' as if to edit it. When in edit mode, hold down Control and Shift, and
then while holding them down, press Enter. This tells Excel that your
formula is in array format. You will know when it works because Excel will
put { } brackets around the entire formula.

Then drag the auto fill cross all the way down to where your data ends, thus
copying the array formula down.

If you get stuck, research CSE (control shift enter) commands on the
microsoft website, there is a ton of information on them.

I know that this is a bit tedious, but it works for what you need, and I
could not think of an easier method.

 

[Tom]

Actually for row C, this is a better formula to use:

=IF(ISERROR(INDEX($A$1:$A$1000,SMALL(IF($B$1:$B$1000="Duplicate",ROW($B$1:$B$1000)),ROW(1:1)))),"",INDEX($A$1:$A$1000,SMALL(IF($B$1:$B$1000="Duplicate",ROW($B$1:$B$1000)),ROW(1:1))))

Just paste it, hold down Conrol and Shift and then hit enter, and then
release all 3 buttons. When it works, there will be {} around the entire
formula.
Copy down.