aubie8
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Where is the data coming from?
If the data is comming from a database then I am sure we can perform the matching before the data is entered onto the spreadsheet, this will be much faster.
If the data is comming from a database then I am sure we can perform the matching before the data is entered onto the spreadsheet, this will be much faster.
ASKER
Thanks for your input john.
the data is just coming from three seperate spreadsheets that get emailed in. so, this the only facility that can be used.
i will look at your code tomorrow. i have got it working. now to just make sure it works as fast as possible.
thanks again for your help!
ps. seems i clicked the refresh or something and added this one twice. sorry about that.
the data is just coming from three seperate spreadsheets that get emailed in. so, this the only facility that can be used.
i will look at your code tomorrow. i have got it working. now to just make sure it works as fast as possible.
thanks again for your help!
ps. seems i clicked the refresh or something and added this one twice. sorry about that.
ASKER
after looking into this. i liked this answer.. although i didn't use it. i am sure i will in the future!
Private Function CountOfMatches(ByRef RangeA As Range, ByRef RangeB As Range) As Long
Dim nTotal As Long
Dim cellA As Range, cellB As Range
For Each cellA In RangeA
For Each cellB In RangeB
If cellA.Text = cellB.Text Then
nTotal = nTotal + 1
Exit For
End If
Next cellB
Next cellA
CountOfMatches = nTotal
End Function
I'll take a quick look for something a little faster