Search Not Just Numbers

Tuesday 17 February 2015

Excel Tip: Identify new entries in a list

I received the following email from a reader and I thought it was a common problem that might make a good blog post.

Gill Carnell of www.secretsofsoftware.co.uk sent me the following email regarding her work with the First Monday Business Network in North Yorkshire:

"I have been running a Free business network event for just over a year now.

I have built up a spreadsheet of approx. 150 unique names/emails of those who have attended on one or more occasions. This main list increases by half a dozen or so each month.

I paste the main list to create an invitation list to promote an upcoming meeting via eventbrite.

After a few weeks I export the event registrations to a smaller spreadsheet. It contains mainly names from my main list plus new entries who have registered for the first time.

My mission is to then send a second invitation to those on the main list who have not registered (not on small spreadsheet).

Currently I've used various combinations of VLOOKP and MATCH functions to find entries on both lists. But then I have to filter or sort and then copy resulting values to create the new mailing list.

Question is whether there is an Excel Function or you can recommend a technique that allows two spreadsheets to be compared and if duplicates occur remove both entries, so I am not inviting people who have already registered? Sort of List One subtract List Two"

I think the simplest approach would be as follows:

Add two new blank sheets to the spreadsheet that contains the Main List, so that you can paste the Registrations list into one of them each time, and use the second for the mail merge.

Add a new column to the Main List to count entries on the second list.

Say that the Main List is on a tab called MainList and  the email addresses are held in column D, whereas the Registrations are held on a tab called Registrations, and that these email addresses are in column F.

The formula in this new column (for row 2) could then be:

=COUNTIF(Registrations!F:F,MainList!D2)

This can then be copied down to ensure that it covers the whole list.

This will count, how many times that email address appears on Registrations. We are only interested in the zeros (i.e. those that don't appear on the Registrations List).

Switch on Autofilter for the main list (if it is not already switched on), and filter the Count column to only show zeros, then copy the list to the Mail Merge tab, which can be already set as the source for your mail merge.

Next time you do this, just delete the contents of the two spare tabs and paste the new registrations in. Your count column and Autofilter will already be set up and you mail merge will already be set up to point at the new list when you paste it into the mail merge tab.

I hope that helps Gill, and anyone else with a similar challenge.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

6 comments:

  1. Could you not just merge both lists on a new combined worksheet (ensuring that a new field is added first to identify main or secondary list). You can then use conditional formatting on the emails ('highlight duplicate values') and then filter by both 'main list' and 'no fill'. This will give unregistered people on main list.

    ReplyDelete
    Replies
    1. There are a number of ways you could do it, but I think the benefit of the approach I've suggested is that it leaves the lists intact, and takes seconds in the future, after the initial setup.

      Delete
    2. Hi Casey
      Many thanks for your suggestion. It is very easy for us 'Excel Lovers' to happily play with formulae, ranges, sheet names and all the associated syntax.
      I think many of my clients will appreciate the solution you outlined which produces the desired results via a more 'visual approach', without the need for a formula.
      Many thanks for taking the time to share the idea, Gill

      Delete
  2. Hi Glen
    Thanks so much for taking the time to recommend the CountIf function as a solution to the task I posed. Tried it out and definitely produces the required results. As you say it leaves the sheets and formulae intact for ease of use when repeating the process several times. In fact I don't mail merge the repeat registrations I copy the resulting list of emails into the eventbrite website to generate the invitations but nevertheless it is a neat, efficient and effective solution.
    Much appreciated, Gill

    ReplyDelete