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".