Quick compare of two excel columns

Posted on 2003-02-25
Medium Priority
Last Modified: 2010-04-07
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
  • 3
  • 2

Expert Comment

ID: 8022220
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

Accepted Solution

JohnMcCann earned 750 total points
ID: 8022547
If both columns are sorted the search will be faster.

I have assumed that you only want the total matches.
I have added a simple time test to it so that if you modify it you will be able to examine the effects on the time taken.

I have also added DoEvents to the loop this will enable you to continue working while it is running.

If you wish I can post a little example that will help you test the performance properly.  But I am afraid that is all I can do.

'Code to paste into worksheet
Option Explicit

Const COL_A_COUNT As Integer = 250 'Range A max
Const COL_B_COUNT As Integer = 250 'Range B max

Private Sub Worksheet_Activate()
Dim n As Integer, nCount As Integer
Dim StartSecs As Long, EndSec As Long, Secs As Long

    With ActiveSheet
        'Fill column A
        For n = 1 To COL_A_COUNT
            .Range("A" & n).Value = n
        Next n
        'Fill column B
        For n = 1 To COL_B_COUNT
            .Range("B" & n).Value = n
        Next n
        StartSecs = Timer 'Start Test
        nCount = CountOfMatches(.Range("A1:A" & COL_A_COUNT), .Range("B1:B" & COL_B_COUNT))
        EndSec = Timer    'End test
    End With
    'How long did it take?
    Secs = EndSec - StartSecs
    'Tell Me
    MsgBox "CountOfMatches = " & nCount & vbCr & _
            "Took a total of " & Secs & " Seconds"
End Sub

Private Function CountOfMatches(ByRef RangeA As Range, ByRef RangeB As Range) As Integer
Dim nTotal As Integer  'Limit of 32,000 and something
'Dim nTotal As Long     'Use if 32,000 is not enough

Dim cellA As Range, cellB As Range
   For Each cellA In RangeA 'Lopp through Cells in Range A
       For Each cellB In RangeB 'Lopp through Cells in Range B
           If cellA.Text = cellB.Text Then 'Test Values
               nTotal = nTotal + 1  'Values are the same increment counter
               Exit For             'Exit Loop through Cells B
           End If
       Next cellB 'End Loop Cells B
       DoEvents 'This stops the PC looking like its crashed
   Next cellA 'End Loop Cells A
   CountOfMatches = nTotal 'Return Matches
End Function

Expert Comment

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

Author Comment

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

Author Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month13 days, left to enroll

777 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