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