Any Excel Experts?

[Reck B]

My company receives weekly data for direct mail campaigns.

The weekly data is pasted onto a master file on seperate weekly worksheets within a workbook in excel.

What i want to be able to do is check each new weekly data file I get across the whole master workbook for duplicates using the postcode and surname fields (so i don't end up sending the same mailer more than once to the same recipient)

Any ideas how i can do this?

or would it be easier to buy MS Access and migrate it all over?

ta.

[tahoma]

Excel is fine for mucking about with temporary data sets, but you need to migrate this to a proper database if you really need to be able to interrogate the data.

Access if fine if you are looking at less than 10,000 records, and is fairly easy to work out yourself. Otherwise SQL Server Express, which is free but you will probably need a developer to help you with a front end.

[Reck B]

Excel is fine for mucking about with temporary data sets, but you need to migrate this to a proper database if you really need to be able to interrogate the data.

Access if fine if you are looking at less than 10,000 records, and is fairly easy to work out yourself. Otherwise SQL Server Express, which is free but you will probably need a developer to help you with a front end.

Ta, we'll be getting 10k records per month, so will speak to our IT man

[Trampa501]

Yes, I agree with Tahoma, a database is the best way to resolve this type of query.

However, if you want to stick with Excel, maybe the answer is to use pivot tables. It's not the only way (you can spend a long time using functions such as Match and Vlookup) but it's the quickest way imo. Of course it will take you a little time to understand how to use pivot tables. Some useful tutorials on the following link

Contextures Tech tips

[Trampa501]

Ta, we'll be getting 10k records per month, so will speak to our IT man

Sql Server is one solution. Another database you could use is Mysql - fairly easy to operate, once you have the Apache server set up (various easy install sites on the web, for different operating systens). Once installed you can go into phpmyadmin and run the sql like this

select surname, postcode, count(*)          from your_master_table          group by surname, postcode           having count(*) > 1 

That should bring back duplicates. Be warned though, you may well come across the problem of inconsistent data. For example someone with the surname O'Neil may be recorded as "O'Neal', ' ONeill' and other variations. Equally the post code for NW3 6FA could be down as 'NW36FAA' or even just 'NW3' That's where you have to hope the data structure and validation has been set up by professionals.

[ccc]

Can you not sort the columns and have a formula that checks the next cell below to see it matches and then display something?

Quick and easy but less hassle for now until you go with a db route.

That is what I would do for a quick and easy solution - requiring no IT help !

Although as others have said duplicates may be spelled a different manner so there are all sorts of things that could cause errors.

You could set up a basic macro to get around most of these - go through all data in a certain column and remove ' or spaces etc..

Who knows. A database is most likely the best bet. But then you need people who know how to use it.

[Fishbone Glover]

Short term, I'd run a query on the original sheet using the import external data command in the data menu, select the fields I wanted, then edit the query myself in Microsoft Query. Under the properties tab you can select 'unique values only', which will remove any duplicates.

Longer term, I think a database would be the way to go.

[Jason]

My company receives weekly data for direct mail campaigns.

The weekly data is pasted onto a master file on seperate weekly worksheets within a workbook in excel.

What i want to be able to do is check each new weekly data file I get across the whole master workbook for duplicates using the postcode and surname fields (so i don't end up sending the same mailer more than once to the same recipient)

Any ideas how i can do this?

or would it be easier to buy MS Access and migrate it all over?

ta.

A database would be easiest, however ...

Are you saying each weekly set of data should be unique, and you want to check that it is unique? If so, VLOOKUP would do it. Just return true if the data exists on the other worksheet.

Or are you getting a weekly list, and you want to only add unique data to your master list?

[pl1]

Maybe I've misunderstood but couldn't you just paste the lot into the master sheet anyway & use filter to hide duplicates from certain columns (in 2007, data tab/filter/advanced/tick box for unique records only)?

[worzel]

Check out Mr Excel on youtube, he does great tutorials explaining everything you could want to know on excel.

[PopGun]

Maybe I've misunderstood but couldn't you just paste the lot into the master sheet anyway & use filter to hide duplicates from certain columns (in 2007, data tab/filter/advanced/tick box for unique records only)?

Isn't there a condional formating function that can highlight rows with duplicate data?

Edit: Conditional formating > Highlight Cells Rules > Duplicate Values

I think a simple Access DB as previously mentioned with update/addend table queries would be the way forward.