21-Dec-08 NEW: Compare 2 lists

Nick's picture



They said it couldn't be done, but here it is!

A list comparison example ONLY using advanced Excel formulae

This example shows you how to compare 2 lists of data and to work out a sorted list of:

  • What's in both lists
  • What's in List 1 and not List 2
  • What's in List 2 and not List 1

Compare 2 lists in Excel

 

AttachmentSize
Compare2Lists.xls30.5 KB

Next time use a database

EOM.

Nick's picture

Most Excel users...

99.9% of Excel users don't want to use databases...

If there's a way to keep it in Excel, they will.

gastonia2695's picture

Compare 2 list (compare2lists.xls

I found that your approach to comparing two list works, but it would have be great if you were to add an explanation of what you were doing with each of the preparatory columns.

Nick's picture

Compare 2 lists - an explanation

ok, here's the explanation...

  • Column A
    • Contains List1
  • Column B
    • Contains a formula:  =IF(ISERROR(MATCH(A2,E:E,0)),0,1)
    • If we find the first element of list 1 in list 2, then return 1 otherwise, return 2
  • Column C
    • Adds a random number to Column B
    • We do this so that we can pick out the unique values later
    • Try removing this random number from the formula, and look at the result
  • Columns E F G
    • Same but for List 2
  • Column I
    • Sorts List 1 descending by our random number column C
    • The ones where we don't have the element from list 1 in list 2 will fall to the bottom.
  • Column J
    • Removes anything from list 1 that is not in list 2
  • Column K
    • An sorts column J alphabetically.
    • Now you have a sorted list of what is in list 1 and not in 2
  • Column L
    • Sorts List 1 ascending by our random number column C
    • The ones where we do have the element from list 1 in list 2 will fall to the bottom.
  • Columns M and N are the same as before - removing the elements, and sorting.
  • Columns O, P and Q are similar to above, but for list 2
  • Columns S T and U essentially remove the "zzzzz" from the other columns

Nick