Jump to content
House Price Crash Forum

Archived

This topic is now archived and is closed to further replies.

Reck B

Any Excel Experts?

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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)?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • Recently Browsing   0 members

    No registered users viewing this page.

  • 284 Brexit, House prices and Summer 2020

    1. 1. Including the effects Brexit, where do you think average UK house prices will be relative to now in June 2020?


      • down 5% +
      • down 2.5%
      • Even
      • up 2.5%
      • up 5%



×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.