Quick compare of two excel columns

Posted on 2003-02-25
Medium Priority
Last Modified: 2010-05-01
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!

Question by:aubie8
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 8022511
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.

Expert Comment

ID: 8022641
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.


Accepted Solution

Jacamar earned 750 total points
ID: 8027631
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
    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.


Author Comment

ID: 8205646
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!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month9 days, 6 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question