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
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

621 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