Home » Forums » Open Source Learning Center » Members Show and Learn Forum

21-Dec-08 NEW: Compare 2 lists

Nick's picture
Submitted by Nick on 21 December, 2008 - 10:24



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
‹ Your assistance Merging data ›
»

Next time use a database

Submitted by Anonymous on 4 June, 2010 - 22:32.

EOM.

»
Nick's picture

Most Excel users...

Submitted by Nick on 6 June, 2010 - 21:38.

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

Submitted by gastonia2695 on 16 November, 2009 - 19:52.

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

Submitted by Nick on 19 November, 2009 - 10:43.

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

 

»