Avatar of aubie8
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
Visual Basic Classic

Avatar of undefined
Last Comment
aubie8

8/22/2022 - Mon
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
ASKER CERTIFIED SOLUTION
JohnMcCann

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
JohnMcCann

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

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
aubie8

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