Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Duplicate Disaster
I have a huge table in excel, about 1500 addresses. The problem is some of
them are duplicates. The bigger problem is that if they are duplicates there is a slight difference in their name. for example 123 designer might be 123 desginer co. but with the same phone number etc. So the phone number is the only piece of data that would be the same for a duplicate. Is there a way to have excel find all the duplicates in the column with phone numbers then delete one of the duplicate rows? i've tried that website with the duplicate tutorials doesn't work. |
#2
|
|||
|
|||
Difficult is which to delete.
I would use conditional formatting to identify the duplicates and then you can decide which to delete. Assume the phone number is in column C, select all the rows in column C and use conditional formatting with a formula of =COUNTIF(C:C,C1)1 -- HTH Bob Phillips "Zip Codes" wrote in message ... I have a huge table in excel, about 1500 addresses. The problem is some of them are duplicates. The bigger problem is that if they are duplicates there is a slight difference in their name. for example 123 designer might be 123 desginer co. but with the same phone number etc. So the phone number is the only piece of data that would be the same for a duplicate. Is there a way to have excel find all the duplicates in the column with phone numbers then delete one of the duplicate rows? i've tried that website with the duplicate tutorials doesn't work. |
#3
|
|||
|
|||
try a helper column next to the telephone number
with =if(countif($B$1:B1,B1)1,"Duplicate","") and copy down to the bottom of your list Select the helper column and using <Data<Filter<Autofilter Select the "Duplicate"s and delete the rows. Since will delete the entries after the first occurance, If you think the first occurance may be wrong you could instead use =if(countif($B$1:$B$1,$B$2000)1,"Duplicate","") This time select both the Tel number column and the helper column before applying the filter After selecting duplicate for the helper column select the fist select each number in the telephone number column and look at the different entries. delete the rows you do not want to keep reselect all in the telephone number column and reselect Duplicate in the helper column. repeat this for each telephone number with a Duplicate by it. "Zip Codes" wrote: I have a huge table in excel, about 1500 addresses. The problem is some of them are duplicates. The bigger problem is that if they are duplicates there is a slight difference in their name. for example 123 designer might be 123 desginer co. but with the same phone number etc. So the phone number is the only piece of data that would be the same for a duplicate. Is there a way to have excel find all the duplicates in the column with phone numbers then delete one of the duplicate rows? i've tried that website with the duplicate tutorials doesn't work. |
#4
|
|||
|
|||
On Mon, 13 Jun 2005 06:54:03 -0700, Zip Codes
wrote: I have a huge table in excel, about 1500 addresses. The problem is some of them are duplicates. The bigger problem is that if they are duplicates there is a slight difference in their name. for example 123 designer might be 123 desginer co. but with the same phone number etc. So the phone number is the only piece of data that would be the same for a duplicate. Is there a way to have excel find all the duplicates in the column with phone numbers then delete one of the duplicate rows? i've tried that website with the duplicate tutorials doesn't work. I assume your columns of data have Labels at the top. You can use the Advanced Filter to do this. 1. Assumption -- your phone numbers are in column B and start in the second row. 2. In some cell, not in Row 1, enter the formula: =COUNTIF($B$2:B2,B2)=1 Be sure there is a blank cell above this. 3. Select some cell in your data table. 4. Data/Filter/Advanced Filter 5. Action: Either is OK but for safety's sake would "Copy to another location. List Range: Your table should be selected. Criteria Range: Select the cell entered in step 2 *AND* the blank cell above it. <OK --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping duplicate rows | Excel Worksheet Functions | |||
Duplicate information | Excel Discussion (Misc queries) | |||
Showing Duplicate Rows | Excel Discussion (Misc queries) | |||
Finding duplicate records in Excel | Excel Discussion (Misc queries) | |||
Duplicate records in Excel | Excel Discussion (Misc queries) |