Finding Duplicates in Excel

[Darren B]

OK is there an easy way of removing duplicates in excel or access

im just running a mailsort on my servers but before i send the data to the mail house i need to remove the duplicates, rather than scan through all the address lines and try to spot them i wonder if there is another way

i have 45,000 records to check and was hoping there is a an easier way of doing this. All addresses are broken down into seperate columns for name, 1st line, town e.t.c

Cheers
D

[jont]

In Help search for "filtering for unique records"

[Darren B]

thanks Jont

i was looking at doing it more like search and match, but thats works a treat, it nicely sorted the data and moved the duplicates to the bottom, just checking its correct but looks good

Cheers Fella
D

[jont]

Are you now down to 53 unique records?

[Darren B]

Cheeky bugger

Actually i have a small DB with 5k which i ran it on 1st, the only problem i have is when i do the large one of about 90k, its to many records for excel so i have to work in access, but thats another time


D

[jont]

Can you highlight a range in Excel and run it on just that?

[Mike Hughes]

I think excel 2007 increases the maximum number of rows from 64K to 1M. That should be enough, even for Darrens mailing list.

Mike

[Darren B]

more than enough mike, i wish it was that big

[Darren B]

I ran it on all the columns, well i selected to copy the unique records only to new columns.

seems to work ok

[pinbrook]

before i send the data to the mail house i need to remove the duplicates

Any decent mail house should have features incorporated into their software that checks for duplicates.

They should also check for bounces, and each time you send them a new list they should have a list of unsubscribes to check for too.

with the correct software theses features take a couple of minutes to run.

[Darren B]

The mail house will offer a dedupe, but we handle everything our end, all returns come to us fo us to amend our system for the companies that have gone bust, moved e.t.c.

It is easier for me to sort the data and get the right number of address prior to going to the mail house, we have 2 catalogues to mail out, a local Kent edition and a national edition. the variation is front cover, we need to control the print run qty closely to ensure we dont run out or over print and this means we need to control the mail out qty closely aswell.

if it was a simple flyer mailshot then i would leave them to it, but the postage costs waisted for a few hundred catalogues being returned mounts up quite quickly.

As for a decent mail house, these guys take them straight from the printer polybag, print and feed into the postal system within a few days, i think they are pretty clued up on what they have to do

[guccij]

I use Dedupe4Excel from DQ Global. Not sure if this would suit you? It works on databases of up to 65,000 so you would still have to split your big one.




So to speak.

[pnagames]

do you have any field that you can use to spot duplicated?

if so in access create a query on your table add all your fields, click the big Sigma on teh top (to bring the totals row into play) and then add one field that you can spot duplications again with a "count" instead of group function

this is on top of my head and it may not work depending the data you have. it certainly works on the millions of records i work with.

D, if you want to send me the db and a brief spec for kicking out dupes & quality checks, i can write the queries for you and send it back to you.

[pnagames]

and by the way,

in excel you can do the same using a pivot table BUT that means you would have to go and manually delete the records after that whether is access you can export the results of your query and delete the last column