Excel - Compare two tables using COUNTIF

Do you have two tables (lists) in Excel, perhaps one is newer or you suspect it is different? You'd like to compare the two tables and see the differences easily? There are a few ways to do this, here I'm going to show how the COUNTIF function can be used, I've found it to be quick and easy to use.

Example
The following is an example where I have two tables in one Excel worksheet. You could have these tables in different worksheets of course. You also don't need to use Tables, this example would also work with ranges. It's just an example, I hope it is helpful to you:

The tables are named Table1 and Table2.

 

Table2 has less rows, only 1714 compared to 1718 in Table1. The question is, which rows are missing?

Enter a title to create a new column (above), a new table column will be created (below).

In the first cell in the new column you can enter the COUNTIF() function.

Enter:

=COUNTIF(Table2[Town], A3)

This means that we want look in the Town column of Table2 for whatever is in A3 (Table1). For example, it will look to see whether "Ampthill" is present in Table2 or not.

Because you entered the function into a column in a table, it'll replicate the formula all the way down. As you can see in the above example there is a result in each row of the column 'In Table2?'. COUNTIF() returns the number of times it found what it was looking for. Therefore you can see that on row 20 there's a zero, this means that "Ascot" could not be found in Table2.

Filter

Filters are automatically available for Tables, this makes it easy to see those results that are zero:

Filter the 'In Table2' column for all the zeros (where it did not find anything in Table2).

Enhance the look

For something quick the 0 or 1 is fine. If you want to share this with a others then you could tidy it up. Here's one way to do this:

Change the formula, add the surrounding IF() statement to set a Yes or No result. 

=IF(COUNTIF(Table2[Town], A3),"Yes", "NO!!!")

Having "NO!!!" makes it easier to spot where Table2 is missing this item.

Conclusion
This is just one way to compare data in two tables in Excel. It's nice and quick, I'm sure it can help anyone who just needs to quickly understand which rows are missing from a data set. 

I used the Excel 365 version as of August 2020 for this article. As the COUNTIF function is not new, I am sure this solution will work in older version of Excel too. That's another reasons it's a good choice.

Wordle - visualise text based data

Have you ever had data and wished you could display it in a more interesting way than the usual pie or bar chart? If the data isn't numbers but words or text then it's not easy either! There's a simple website called Wordle (www.wordle.net) where you can copy/paste words and it'll make a word image with them - sometimes called a "word cloud". The word that appears most in your list will be biggest. The word that appears less will be smaller. Instantly you can see which is more popular from a list of words. OK, enough words here and let's see an example:

I went to CNET and found the smartphone market share figures for the 3rd quarter of 2012:
http://news.cnet.com/8301-1035_3-57544131-94/android-beats-ios-5-to-1-in-q3-smartphone-market-share/ [accessed 28/11/2012]

I added the figures into Worlde's Advanced page like this:

As you can see above, you can write text, a colon, followed with percentages or any comparative numbers. The above are percentage of market share by smartphone operating system. Here's what Wordle made of these figures:

Maybe it wasn't the best example because Android has a huge 75% of the market and poor Windows Phone at the other end of the scale has only 2% so you can hardly see it (it's above the "o" in Android).

But can you see the potential? Of course the more data you put in the more dense the resulting graphic will be. You can also change the layout and direction of the text in the final image. The colour and font can also be changed. It gives you the possibility of expressing textual data in an interesting way. In the above example it's so obvious that Android has the biggest market share, you don't need to know any numbers, you can tell by the size of the word Android. Extremely visual!

Try it for yourself at www.wordle.net