38,000 Records! Please Help me eliminate Duplicates

New user needs help putting macro in and running it

[Uugggh]

I am a new user of excel and have seen postings reguarding the elimination of duplicates.

The duplicates I have can only be determined by looking at two columns together. One column contains a location (often repeated), the other contains a shelf(often repeated), it is the combination of the two that would make a record unique.

I have never run a macro and am unsure where to put the codes I found here.

Will the macros I found on the site do what I am trying to do? If so where do I post them? Thanks in advance for your assistance.

[cfx211]

I don't know a thing about macros, but access can do this real quickly.

Load your spreadsheet into access as a table. Create a query with that table where you drag your two columns into the query fields. Hit the group by button (its the sigma icon). This will then pull a deduped combination of all your records. Copy that and past it back into excel.

[Uugggh]

Thanks for the quick reply. Unfortunately when I did it by the whole spreadsheet it gave me back all 38,000 records. When I did it by Location and shelf. it gave me much less (22,000) but that only contains location and shelf information leaving all other info behind.

[cfx211]

Did you create a primary key when loading this into access? If you did and you have that as a column in your table and you drop all the records in, then you will get all 38k back.

If this is the case then do not drag the column called ID down, and run your query again with the other columns that you need.

[Uugggh]

Nope, no Primary key. Oh well. So much for access, that was disappointing. Thanks for the tip though I will use it infuture for other stuff I have to do.

[ergophobe]

I can barely get excel to do simple addition, but may I suggest

microsoft.public.excel.programming [groups.google.com]

[coopster]

There are some other suggestions in this relative thread:
[webmasterworld.com...]

[Mike_Levin]

cfx211 has the right trick. This is standard database primary key stuff. Just make a database in access. Make the primary key a combination of those two fields, and copy and paste the data from Excel to Acess (no, 38,000 records isn't too much to copy & paste). Access will give you an error message about primary key violations, and a way to supress those messages. Supress them, and the resulting pasted table is de-duped. All the duplicates are put in a new paste_errors table.