How to Compare Two Lists in Excel

When analyzing data you often have to compare two lists to see if

  • They both contain the same data
  • You’re missing data from one of the lists
  • There are any duplicates or misspellings
  • etc..

If you’re looking to compare two lists for equality, you can use simple comparison operators.

compare lists excel

By turning column C into a helper column, we can compare the lists in A and B by simply typing

  1. =A2=B2
    

and auto-filling that formula down the column. As you can see this works for text, numbers, and dates the same way.

If you need to check to see if certain values exist or not in the other list, I’d suggest using ISNUMBER and MATCH together to find your answers.

Let’s separate the two columns for a second and add some additional values to List #2:

compare lists excel_2

We’re going to do a check to see which values in List 2 are in List 1.

We’ll create a helper column in Column D and first use the MATCH function to return the ROW that a match exists on, if any.

compare lists excel_3

  1. =MATCH(C2,A:A,0)

Excel searches for each of the values in column C inside of column A. The numbers returned are the ROW in column A where a match was found, while #N/A simply means there was no match found.

By wrapping this function in the ISNUMBER function, we can simplify this to only return TRUE (if a row number is found) or FALSE (error, no match).

compare lists excel_4

  1. =ISNUMBER(MATCH(C2,A:A,0))

We can now see exactly which values exist in both List 1 and List 2.

Using SUBSTITUTE and CHAR to remove a block of unwanted text

The SUBSTITUTE function is incredible for cleaning up text. You can replace a string of text with whatever you’d like, and if the text isn’t found you don’t get an error; It just returns the original string unharmed.

The CHAR (as in “character”) function is a pretty cool weapon as well. You can specify which characters you want to target, even if they’re weird characters, numbers, letters, or paragraph breaks. You’ll see why this is important shortly.

Examples of Random Characters and their character numbers

  • Lowercase “t” = Character Number 116
  • Uppercase “T” = Character Number 84
  • Paragraph Break = Character Number 10

These character numbers allow Excel to target very specific parts of text strings.

Problem:

Remove the second instance of a URL from a block of text

substitute function

Imagine we don’t need to show two instances of the same url in one cell. Let’s erase the second one using substitute

substitute function

Here’s the formula:

=SUBSTITUTE(E2,”DunderMifflin.com”,””,2)

Within E2, we’re substituting the string “DunderMifflin.com” with double quotes – “”. That means we’re essentially replacing the url with nothing. The 2 you see at the end of the formula signifies that we’re replacing the 2nd instance of the url and NOT the first.

While it may look like we’re done, you’ll can see a extra paragraph break that we haven’t replaced with anything yet. Let’s use our friend CHAR() to do that.

substitute function

Looks Good! Let’s analyze the code:

=SUBSTITUTE(E2,CHAR(10)&CHAR(10)&”DunderMifflin.com”,””)

This formula is close, but CHAR(10) is what Excel knows to mean a paragraph break. Using the ampersand sign is a quick way to concatenate two values together. So instead of looking for the url by itself, we’re looking for two paragraph breaks followed by the url, then replacing that entire block of text with nothing. Also note we no longer need the “2” inside of our formula because that block of text does not appear again.

While it may seem much easier to simply do a find and replace in this instance, the substitute function will come in very useful if you have to clean hundreds of rows at once during a deeper analysis. Further, I do not know of a way to delete a paragraph break using CTRL+H, so you’d still be left with huge paragraph breaks at the end of each block of text.

 

What is a Pivot Table?

A pivot table is a feature of Microsoft Excel that allows a user to quickly summarize and analyze a large set of data.

Everything that a pivot table does can be done by hand or by using other Excel formulas. However, pivot tables often speed up the process and allow the person receiving the report to quickly identify what’s important in the data.

Check out our more in-depth series on Pivot Tables:

  1. Pivot Tables – Part 1 – Setup and Features
  2. Pivot Tables – Part 2 – Conditional Formatting