Macro for finding duplicate names

[Guest]

Can anyone help me with writing a macro to find duplicate names in
spreadsheets with 6,000+ entries?

 

[Randy Harmelink]

A lot more details would be needed before anyone could help. For one
thing, you may not even need a macro. A simple COUNTIF() formula in
an adjacent column could find them easily. If you need to delete them
outright, then you could do a sort on the COUNTIF() formula, which
would need to be setup to only count previous entries that match.

If you need to combine lines with duplicate names, then a macro may be
needed. But again, it depends on the details of what you have.

If all you need to do is get a unique list of names, then the advanced
filter or pivot table may be the easiest way to go.

It also depends on whether it's a one-shot or ongoing need. If
ongoing, maybe a data validation could help to prevent the entry of
duplicate names.

 

[Guest]

Thank you for your response. I am a fairly unsophisticated user of Excel --
I can do basic tasks. Is there a good place to find how to use formulas? I
don't even know how to go about writing a formula. I am in charge of taking
37 different email (Excel lists) and merging them together, deleting
duplicates and then bringing all of the information into an access database.
It's a bit overwhelming. I did look at a pivot table, but it was at the end
of the day, I was tired and gave up. I'm sure that I can do this job more
quickly and efficiently, but I don't have all of the tools to do it.

Thanks again.

Brenda

 

[Guest]

If you are intending to upload the data into Access then there really is no
need to make the seperate spreadsheets into one big one. Upload the first
sheet into a table. Set the primary key on the table to the name field which
contains duplicates. Now start uploading the remaining sheets. The uploads
will encounter errors on the duplicate names and just exclude those records
from the upload...

 

[MH]

What if there are two John Smiths?

Using names as PKs is not a very good idea, the best way to tackle this
problem is to import all the data into Access and do a "duplicates query" to
identify the offending rows.

MH

 

[Guest]

Point taken... and I agree whole heartedly. The question was how to remove
duplicates. Your solution adds a bit more validation into the process and
depending on the source data may be a better solution.

 

[Guest]

U dont' need a macro for this ! just follow my steps.

Step1: Sor the names in any order (Data--> Sort)

Step2: at the most-right-hand column, input the following formula

=IF(A2=A3, "duplicated", "ok") , so ur names should be at column A.

Step3: Drag the formula till the end of the column, and u'll see. If there
are duplicated names, the column with our formulas will show "duplicated" so
those names do not duplicate will show "OK" .

Step4: Filter it (Data --> Filter--> Auto Filter). Go to ur right-most
column, filter out duplicated and high light them

Step5: Take out the filter. DONE !!!!!


Now u have duplicated names highlighted, or vice versa ^_^!.

Allen P. W.

Deutsche Bank AG, Bankok.

(e-mail address removed)
(e-mail address removed)