MS Excel Remove duplicate names 1

[Pccoordinator]

How can I take data (specifically last name, address) and remove all lastname and Address that are indentical?

 

[snyderj]

You can use Data > Filter > Advanced Filter from the menu, then select "unique records only".

 

[DaleWatson123321]

Hi Pccoordinator,

Because the Advanced Filter option can be "more involved", I thought to offer the following:

First, the option to "Filter-in-Place" will NOT be an option if your data includes more than just LastName and Address. I assume that it would also logically include other fields like City, Phone Number, Account Number, Reference Number or whatever.

If you indeed have more fields, and attempt to "Filter-in-Place", you will NOT succeed ...because Excel will assess ALL the fields (not just LastName and Address).

If you were to include ONLY the LastName and Address as your "data" range, then the result will be "disjointed" data - i.e. the "Filter-in-Place" option would remove duplicates, but would leave the other fields "un-filtered".

THE SOLUTION: To use the "FilterCopy" option which will EXTRACT the ENTIRE database to a SEPARATE range or sheet.

The following code works:

Sub Remove_Duplicates()
Application.ScreenUpdating = False
Range("data&quot.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:="duplicate_crit", _
CopyToRange:=Range("output&quot, _
Unique:=True
End Sub

The names in quotes above are Range Names ...defined by using "Control - F3".

"data" refers to your database, where the top row is the row with your field names.

"duplicate_crit" is your criteria, which will have your LastName and Address as your field names, and the cells immediately below these field names need to be included in the name, but are to be left BLANK.

"output" refers to the range where you want the new set of data (in your case, a unique set of data, without duplicates). Assign the name "output" to just the top row, which needs to contain a copy of the field names from your "data". You have the option to include only the field names you wish to include.

Please NOTE: The extraction process works such that any data immediately below the extraction range (in this case, "output&quot will be DELETED.

A further note: The Range Names I used were entirely "arbitrary" - i.e. you can use whatever names you desire. However, be aware that there are some names which Excel considers "reserved names" ...such as "database" and "criteria". Therefore do NOT use these names. For a full list of the reserved names, consult Excel's HELP.

Hope this is helpful.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Pccoordinator]

Thank you for the information Dale Watson. Unfortunately I am a novice Excel user. Most of the instructions you gave were not too clear.

I guess I need to find someone to do this or set this up for me. Any ideas.

Thanks

Ed