Quick compare of two excel columns

I have two columns in excel.  Column one is roughly 2500 rows and column two is roughly 10,000 rows.  Column two is sorted in alphabetical order.

I am looking for the quickest way to compare the data in column one and see if it is in column two.

Would it matter if column one was also sorted?

Of course, I could just start at the top of column two and go down each time, but that takes way too long.

I would REALLY like to do this recursively if possible, but it has been quite a while since I have done that and would need some good code to look at.

I don't think this is HARD, but because of time limitations, I will reward alot of points for fast code, easy to understand and comes back here quickly.  And if allowed, bonus points if its recursive and enough explanation for an old programmer to follow it quickly.

Thanks you guys!

Chris
aubie8Asked:
Who is Participating?
 
JacamarConnect With a Mentor Commented:
ok, here's the steps.  Assume Alphabetical order for both rows.  This example is just for comparing 2 arrays.  Adjust accordinly to fit your excel columns.

dim Value(1 to 2500) as String  '  This is first column
dim ListValue(1 to 10000) as string ' This is Second Column
dim BoolFound(1 to 2500) as Boolean
'  BoolFound will keep track of which items are in the second column.

inFound = 1   '  Start with item 1 in column 2

For inI = 1 to 2500

For inJ = inFound to 10000
 
  If value(inI) = ListValue(inJ) then
    inFound = inJ + 1
    BoolFound (inI) = True
    inJ = 10000
  Else
    BoolFound(inI) = False  
  End if

Next inJ

Next inI

This will go through all 2500 items and check if they are there.  After this, you can check which ones are missing, because their BoolFound() will be false.  

Let me know if this helps.

Jacamar
0
 
MrNedCommented:
sorry, dont have time to write code. im not sure if the built in Excel functions are very fast. if it were me, i would write a binary search algorithm.

google on binary search and you should find heaps of examples. it is not recursive and you shouldnt need that anyway.
0
 
JacamarCommented:
It would definately help if both rows were in alphabetical order.  That way, after you check the first item in the list, you will beable to resume your search from where it was found in colum 2.  You will only have to go through the list of 10000 once and you will know if they are all there.

Jacamar.
0
 
aubie8Author Commented:
this was good even if hard for ME to follow...  I did figure out a way to do this for myself too.  Thanks for the help!
0
All Courses

From novice to tech pro — start learning today.