There are a few times each year that I have to do comparisons between multiple excel sheets.
For example, we need to compare the last two years of PTP attendees against our mailing list to remove duplicates and compile a usable mailing list.
This post has become one of my most important resources every year. I’m terrified that one year I’ll pull up this link and the site will be dead, so I’ll be copy/pasting his steps below. Once again, visit his site (BigHungryGeek). He’s way smarter than I am!
How to do it
- Arrange the lists in two columns with List A in column A and List B in column B.
- Create a third column in column D called List C (leave column C blank for easier readability).
- In cell D5 enter the formula:
=IF(COUNTIF(B:B,A5),A5,0)and press Enter.
- Select cell D5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Equal To…
- In the Equal To dialog box, type 0 and click OK.
- Select cell D5 down to the end of the lists and press Ctrl+D to copy the formula and conditional formatting down.
A name in List C means the corresponding name from List A was also found within List B. In the screenshot above, cell D5 displays the “Moon Barrientos,” which means that name was found on both List A and B. On the other hand, cell D12 displays which means the corresponding name from List A (in this case, “Aracely Rock”) does not exist within List B.
This example demonstrates how to determine if a particular value – in this case a name – in List A also exists within List B. To find the opposite – whether a name in List B also exists within List A – simply change the formula in cell D5 to:
=IF(COUNTIF(A:A,B5),B5,0) and press Enter. Then copy that formula down to the end of the lists.
How & Why it Works
Our formula makes use of two functions,
IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
COUNTIF counts the number of cells within a range that meet the given condition.
COUNTIF has two required arguments, or inputs, to work:
range(where should Excel look, B:B in our example)
criteria(what should Excel find, A5 in our example)
COUNTIF checks column B (using B:B checks the entire column) for the name in cell A5. If it finds the name (i.e. the
criteria you specified was met) it returns 1, otherwise it returns 0. Note: when
COUNTIF returns 1 or 0, Excel treats that as TRUE (1) or FALSE (0).
IF function has three required arguments, or inputs, to work:
logical_test(any value or expression that can be evaluated to TRUE or FALSE)
[value_if_true](what value should Excel return if if
[value_if_false](what value should Excel return if
In our example
COUNTIF is the
logical_test that gives the
IF function either a 1 or 0, depending on whether it finds the name we specified from cell A5 within column B. If it finds the name (i.e. the
logical_test is TRUE), we specified A5 as the
[value_if_true] so Excel will display that name and move on. If it does not find the name (i.e. the
logical_test is FALSE), we specified 0 as the
[value_if_false], so Excel will display 0 and move on.
You can quickly see a list of names missing from List B by filtering List C to show only “0.” The conditional formatting just makes it easier to spot the missing values.