Comparing lists in Excel 2011

At work we have a newsletter that we send out three times a year. We send it out in both print and E:Mail. We’re trying to get as many people as possible to switch to the E:Mail edition as this cuts costs, and inbetween issues several people have switched to E:Mail. I wanted to compare the e:mail addresses on our customer database with those in our mailing list software, and add those that did not exist. To do this I used Excel conditional formatting feature to highlight the addresses that only appeared on the customer database and not in the mailing list

The two lists

Note: Both of these lists in this example are the same length, but this will work on lists of different lengths

Select the data in the first list

Remember – you only want the data leave the header row unselected

From the Insert Menu, choose Name then Define

Enter the name for the range of data, then click OK

You can’t have spaces in your name but you can use the _ character

The new name will appear in the Cell Reference box

I’ve since found out you can just type the name straight into the cell reference box.

Now repeat this for the data in the second list

Select the data in the first list again

In the Home Tab, click on Conditional Formatting and choose New Rule

Choose Classic as the Style

Choose Use a Formula to determine which cells to format

Enter the formula =countif(frommailinglist, a2)=0

Replace frommailinglist with whatever you have called your second list
You can customise the format, but for this example we’re going to use the default
Click OK when you’ve finished

The records that are in the database list but not in the mailing list are highlighted red.

If you want to see who’s in both lists, simply change the formula to =countif(frommailinglist, a2)=1


Leave a Reply

Your email address will not be published. Required fields are marked *