Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing duplicates from a list of addresses
I have a database of 6000+ addresses that includes many duplicates. I need
to delete the duplicates. The problem is the addresses weren't entered with common formatting (Road vs. Rd, Lane vs. Ln, (555) 555-5555 vs. 555-555-5555) which throws off excel's function for scanning and removing duplicates. The silver lining is one column does contain a standard identifying digit that would be the same no matter how the address, phone number, zip code or contact name was entered. How do I remove the duplicate rows using just that one column of data as the criteria? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing duplicates from a list of addresses
Here's one way:
1. Make a backup copy of your data, as a disaster recovery tactic. 2. If you need the addresses to stay in their current order, insert a column and number each row. 3. Suppose the column that contains your silver lining number is column F. Sort all rows on this number. 4. Insert a new column next to F, let's call it G, and enter this formula: =IF(F2=F1,"XXXXX","") then copy and paste it into all rows. It marks duplicates with the XXXXX entry. 5. Sort on column G so all the XXXXXs are grouped, then delete them. 6. If you need to return the rows to their original order, sort on the column you created in #2 above. DaveO Eschew Obfuscation |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge list while removing duplicates | Excel Worksheet Functions | |||
merge list while removing duplicates | Excel Discussion (Misc queries) | |||
removing duplicates from a list | Excel Discussion (Misc queries) | |||
Removing Duplicates from a list | Excel Worksheet Functions | |||
Removing house numbers from addresses | Excel Worksheet Functions |