Link to home
Start Free TrialLog in
Avatar of 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!

Avatar of JohnMcCann

This is a slow way to count matches

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
Avatar of JohnMcCann

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of aubie8


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.
Avatar of aubie8


after looking into this.  i liked this answer..  although i didn't use it.  i am sure i will in the future!