I have a excel sheet with 4,000 names and phone numbers, I want to find and delete duplicates by phone?
Written by General Seo on Feb 28th, 2010 | Filed under: Duplicate Content
number.. I tried the option below, however it does not eliminate all the duplicate numbers. Please let me know how to use a formula or macro. Thanks
Select the entire column you want to delete duplicates from
-From the menu select Data->Filter
- In the box check unique records only.
- That will remove the duplicates.
- Now copy the resulting contents (without duplicates) into a new column (or sheet).
Here is one way to do it. It uses Sorting, Paste-Value, and Split Windows, some of which you might not be familiar with.
1. First, split the screen by clicking Window–>Split.
Click in the lower left pane that appears, and scroll it to the last row of data. Likewise, scroll the upper panes to the first row of data if they are not already there.
2. Next, sort the data according to phone number. That will put all the duplicated numbers next to each other. The split windows help with selecting all the rows of data for this.
3. Starting with the 2nd row of data, add something like the following formula to the spreadsheet:
=if(c2=c1,"duplicate"," ")
(This example formula would be in row #2, with phone numbers in column C. You’d need to use whatever column your sheet uses for phone numbers, instead of column C).
If the phone number is the same as the one in the row just before it, the word "duplicate" will appear in the column with this formula.
4. Copy-and-paste that formula to all remaining rows of data, except for the 1st row. Now, all duplicate numbers will have the word "duplicate" appearing in the column.
5. Copy this column of data, and do a "paste special" to copy the formula Values into the next column:
Edit –> Copy ( or CTRL-c )
Edit –> Paste Special
Choose "Values" in the Paste-Special table that pops up, then go ahead and click "OK" to do the paste.
This will copy the actual word "duplicate" into the next column, rather than the formula, which is important for the sorting that happens in the next step.
6. Do another sort, this time sorting on the column that you pasted values into ("duplicate" for the duplicated numbers, and a space character " " for the non-duplicated numbers).
7. Now all the duplicated numbers are grouped together at the bottom of your table. You can select them as a single large block of cells and delete them.
“lesbian bdsm…
You do not have to be tied up::.”…