Consulting

Results 1 to 4 of 4

Thread: Fast compare values of Non-Contiguous Cells row in two different workbooks

  1. #1

    Fast compare values of Non-Contiguous Cells row in two different workbooks

    My worksheet 1 contains around 2000 rows in column1 and same no of rows contained in worksheet 2 in column1 Is there any way i can compare value(string or integer) in particular row in work sheet 1 to check it is present in any one the row in work sheet 2 I have written one sample vba code for this but its taking considerable amount to time to get the result

    Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
    Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String, rowval1 As String, rowval2 As String
    Dim report As Workbook, difference As Long
    Dim exact As Long
    Dim row As Long, col As Integer
    With ws1.UsedRange
    ws1row = .Rows.Count
    ws1col = .Columns.Count
    End With
    With ws2.UsedRange
    ws2row = .Rows.Count
    ws2col = .Columns.Count
    End With
    difference = 0
    exact = 0
    For row = 1 To ws1row
        rowval1 = ws1.Cells(row, 1).Formula
        For row1 = 1 To ws1row
            rowval2 = ws2.Cells(row1, 1).Formula
        If rowval1 = rowval2 Then
            For col = 1 To ws1col
    
    
    
    
            colval1 = ""
            colval2 = ""
            colval1 = ws1.Cells(row, col).Formula
            colval2 = ws2.Cells(row, col).Formula
            If colval1 <> colval2 Then
                difference = difference + 1
            Else
                exact = exact + 1
            End If
            Next col
        End If
        Next row1
    Next row
    
    
    
    
    Debug.Print difference
    Debug.Print exact
    'Debug.Print ws1row
    'Debug.Print ws1col
    
    
    
    
    
    End Sub


  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    If you want to compare the values you could use a VLookup formula to see if there is a match in the second column, if there is no match you would get an N/A error so you can wrap the formula in an IFERROR formula.

    =IFERROR(VLookup(), "No Match")

    Then filter for the No Match value.

    With VBA you could use the formulas or do a loop to find the values:

    For example:
    Dim Cel AS Range
    
    For i = 1 to 2000
    Set Cel = Workbooks("workbookname").Sheets("sheetname").Range("target range to look at").Find(What:=Range("A" & i).Value, LookIn:=xlValues, LookAt:=xlWhole,MatchCase:=False)
    If Cel IS Nothing Then
    'This is a Non Match
    End If
    Next i

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I responded to this post yesterday. Where has it gone?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Thanks for reply it solved my problem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •