LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default How do I remove duplicates

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Remove duplicates jennifer Excel Worksheet Functions 2 August 28th 09 07:26 PM
Remove Duplicates Joe Excel Worksheet Functions 2 February 13th 09 11:58 PM
remove duplicates BlindShelter Excel Discussion (Misc queries) 2 December 19th 08 08:45 PM
Remove Duplicates dk New Users to Excel 15 March 28th 08 07:41 AM
How to remove duplicates? Lakewoodsale Excel Discussion (Misc queries) 2 January 25th 08 10:31 PM


All times are GMT +1. The time now is 06:25 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright 2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"