ANDs, ORs, and IFs: Comparing big lists in Excel

One problem we face when manipulating large amounts of data in Excel is checking to see if two lists of the same length contain the same items. For instance, we might be given a list of products that a company has for sale this month, running to thousands of items, then the following month, we get another list of products for sale and we need to see if there has been any change between those two lists. This isn’t too hard to deal with when you only have a hundred or so items, but it gets a little thorny when your list runs to tens of thousands.

What we do is line the two lists up, side by side, in sorted order.

Two lists

Use the simple “A1=B1” formula to compare pairs of items in the lists.

Formula for comparing elements in the lists

If the pairs are the same, this will be true, otherwise they’ll be false.

Results of comparing elements in the lists

Copy this formula down for all your rows. Then use the AND function and give it the entire range of comparison formulas.

Checking comparisons

This will only be true if every single one of the values in your list are exactly matches. If even one comparison is false, this big AND statement will evaluate to false.

This is a quick and dirty approach. For tougher problems, we use a slightly more complicated formula in the comparison where we evaluate it to 1 if the value is true, 0 if the value is false. This gives us more flexibility to combine comparisons, but that’s a topic for another post.