Reck B Posted March 16, 2011 Report Share Posted March 16, 2011 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. Link to post Share on other sites
tahoma Posted March 16, 2011 Report Share Posted March 16, 2011 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. Link to post Share on other sites
Reck B Posted March 16, 2011 Author Report Share Posted March 16, 2011 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 Link to post Share on other sites
Trampa501 Posted March 16, 2011 Report Share Posted March 16, 2011 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 Link to post Share on other sites
Trampa501 Posted March 16, 2011 Report Share Posted March 16, 2011 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. Link to post Share on other sites
ccc Posted March 16, 2011 Report Share Posted March 16, 2011 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. Link to post Share on other sites
Fishbone Glover Posted March 16, 2011 Report Share Posted March 16, 2011 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. Link to post Share on other sites
Jason Posted March 16, 2011 Report Share Posted March 16, 2011 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? Link to post Share on other sites
pl1 Posted March 16, 2011 Report Share Posted March 16, 2011 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)? Link to post Share on other sites
worzel Posted March 17, 2011 Report Share Posted March 17, 2011 Check out Mr Excel on youtube, he does great tutorials explaining everything you could want to know on excel. Link to post Share on other sites
PopGun Posted March 18, 2011 Report Share Posted March 18, 2011 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. Link to post Share on other sites
Recommended Posts
Archived
This topic is now archived and is closed to further replies.