Quick ways to search and find a value in a larger data set - VBA

user_14361287 0
Viewed 171

I have two columns containing alphanumeric data - Column A in workbook1 and Column A in workbook2.

Column A, in workbook1 contains 40,000 rows(contains duplicate values, but they are needed) and column A in workbook2 contains 25,000(contains unique values) rows. I have to search whether Column A values are present in column B. If yes i have to update the Column B in workbook1 as user_found.

I tried looping but as the size of data is large, Excel crashes frequently and it takes a lot of time. Please help i am a beginner.


    Dim arr As Variant

    With AAws1
    arr = AAws1.Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    End With

    Dim varr As Variant

    With userws1
    varr = userws1.Range("A4:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
    End With

    m = 4
    Dim x, y, match As Boolean
    For Each x In arr
        match = False
        For Each y In varr
            If x = y Then
            match = True
            AAws1.Cells(m, 4).Value = "user found"
           End If
'           m = m + 1
        Next y

        If Not match Then
            AAws1.Cells(m, 4).Value = "Not found"
        End If
        m = m + 1
    Next
vba excel
1 Answers

Flag Found Values

  • This will create a reference to the Source Lookup Column Range (srg) containing unique values and use this range as the second parameter in Application.Match since it is several times more efficient on a range than on an array. The values in the Destination Lookup Column Range (drg) will be written to an array (dData) which will also be used as the resulting array (dData). After the results have been written to the array (dData), its values will be copied to a given column (dCol) i.e. to the Destination Range (drg.EntireRow.Columns(dCol)).
Option Explicit

Sub FlagUnique()
    
    Const dCol As String = "B"
    Const dFlag As String = "user found"
    
    ' Source - unique
    Dim srg As Range
    With userws1
        ' 'Application.Match' is multiple times faster on a range
        ' than on an array.
        Set srg = .Range("A4", .Range("A" & .Rows.Count).End(xlUp))
    End With
    
    ' Destination - duplicate
    Dim drg As Range
    Dim dData As Variant
    With AAws1
        Set drg = .Range("A4", .Range("A" & .Rows.Count).End(xlUp))
        dData = drg.Value
    End With

    Dim dValue As Variant
    Dim sIndex As Variant
    Dim r As Long
    Dim IsFound As Boolean
    
    For r = 1 To UBound(dData)
        dValue = dData(r, 1)
        If Not IsError(dValue) Then
            If Len(dValue) > 0 Then
                sIndex = Application.Match(dValue, srg, 0)
                If IsNumeric(sIndex) Then
                    dData(r, 1) = dFlag
                    IsFound = True
                End If
            End If
        End If
        If IsFound Then
            IsFound = False
        Else
            dData(r, 1) = "not found" ' I would prefer 'dData(r, 1) = Empty'
        End If
    Next r
    
    drg.EntireRow.Columns(dCol).Value = dData

End Sub
user_98140690
Related