none
compare two columns in two worksheets in the same workbook RRS feed

  • Question

  • I am using excel 2013 and have two worksheets in the same workbook.Using vba I need to compare columns

    sheet1.columB to sheet3 columnD when a match is found I need to make sheet1.columC=OK abd sheet3.columnF=OK.

    Is there a better way than using the for next loop in vba?

    Monday, September 29, 2014 2:06 PM

Answers

  • Hi o2code,

    I want to know : if each value in sheet1 column B is found anywhere in sheet3 column D

    To acheve this goal, I suggest that you loop the cells in Column B and check whether it containd in Column D.

    Here is a sample for your reference:

    Sub test()
    
    Set SourceRange = Application.Intersect(Range("A:A"), ActiveSheet.UsedRange)
    Set TargetRange = Application.Intersect(Range("D:D"), ActiveSheet.UsedRange)
    Dim contained As Boolean
    contained = True
    For Each aCell In SourceRange.Cells
    
        If Not IsEmpty(aCell.Value) Then
            Set c = TargetRange.Find(aCell.Value, LookIn:=xlValues)
            If c Is Nothing Then
                contained = False
                Exit For
            End If
        End If
    Next aCell
    
    Debug.Print "Column A is contianed in Column D:" & contained
    End Sub
    
    
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 7, 2014 12:50 PM
    Moderator

All replies

  • Hi.

    Do you mean that you want to compare the columns by cell, as in sheet1!B1 = sheet3!D1, sheet1!B2 = sheet3!D2, etc.?  Or do you want to know if each value in sheet1 column B is found anywhere in sheet3 column D?

    Monday, September 29, 2014 9:28 PM
  • I want to know : if each value in sheet1 column B is found anywhere in sheet3 column D

    Monday, September 29, 2014 11:38 PM
  • Does it have to be done using VBA?

    I'm going to assume that column B on sheet 1 and column D on sheet 3 have a set number of rows filled and that each cell has a value (i.e. it isn't an empty cell).  For my example, both have 30 rows.  And I am also going to assume you have two defined names for each source column, let's call them Sheet1Src and Sheet3Src

    So Sheet1Src = "Sheet1!$B$1:$B$30" and Sheet3Src = "Sheet3!$D$1:$D$30"

    In cell C1 on Sheet1, enter this formula:

    =IF(ISERROR(MATCH($B1, Sheet3Src, 0)), "", "OK")

    Copy that formula or fill down for all 30 rows in column C.

    Likewise, on Sheet3, in cell F1, enter:

    =IF(ISERROR(MATCH($D1, Sheet1Src, 0)), "", "OK")

    and copy or fill down to fill 30 rows in column F.

    If your source columns could possibly contain empty cells then you'd want to add a check for that.  Or if it must be done using a VBA macro, you could either set the above formulas for column C on Sheet1 and column F on Sheet3 using VBA.  Or if you want to use VBA and a loop, I can give you my suggestion for what I think would be the fastest version.

    Tuesday, September 30, 2014 2:48 AM
  • Thank you very much for your suggestions.

    The project is - Bank Reconciliation - so there are a number of sheets to be compared -each sheet has nearly the same format but the columns being used are different and an unknown number of lines on each sheet.

    ie (deposits to statements - cheques to statements)-

    Using the Deposits to statementsI am looking to search the statements looking for matches on the columns with xreference and amount - if a match is found then a 'OK' is put in  another column on both the statements deposit sheet.

    When the search is done I want to clear a sheet called 'outstanding' and using the statement if the column = "OK" then add this to the outstanding sheet.

    I am loading the data using vba and would like to do the search/compare with vba so additional suggestions would be great,

    Again many thanks and awaiting your reply.

    Tuesday, September 30, 2014 11:45 AM
  • For/next certainly works.

    I did a quick search and found this thread at StackOverflow that offers at least a couple of other methods.

    http://stackoverflow.com/questions/12642164/check-if-value-exists-in-column-in-vba

    Tuesday, September 30, 2014 4:10 PM
  • I don't quite understand all the parameters of what you need the macro to do.  Do you have some working code you could post and then we could see what improvements could be made?

    Some general ideas: 

    You could create dynamic named ranges on each sheet for the appropriate source column, scoped to that worksheet.  Something like "ReconSrc", which could be column B on one sheet and column C on another sheet.  That way when you are comparing two sheets, you only need to get the range "ReconSrc" for that sheet to know the source column.  And since it would be a dynamic range, it would size itself according to how many rows have values.  (This is easier if each column does not have blank cells that need to be skipped over.)  If the destination column for the "OK" value is always one column to the right that's pretty easy to do with Range.Offset.  Otherwise you could likewise have a range on each sheet called "ReconDest" that is set to the appropriate column.

    I've seen some comparisons of different search methods over on StackOverflow but which one is absolutely the fastest could depend on which version of Excel is being used.  I'm inclined to think that WorksheetFunction.Match would be pretty fast but I could be wrong.  One difference I would make in the examples that Dougbob linked, I would copy the values of each range into a variant array.  Looping through that array should be much faster than looping through the range and pulling the value of each cell over and over.

    Again, those are just some general ideas.  If you have some working code to tinker with that would help a lot.  But even then, I think you are going to have to try a few different methods and time the results when working with some real sample data to see which method proves to be fastest/best.

    Tuesday, September 30, 2014 7:27 PM
  • Try this.

    Sub checkrev()
    
    With Sheets("Sheet1")
    Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Sh1Range = .Range("A1:A" & Sh1LastRow)
    End With
    With Sheets("Sheet2")
    Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Sh2Range = .Range("A1:A" & Sh2LastRow)
    End With
    
    'compare sheet 1 with sheet 2
    For Each Sh1cell In Sh1Range
    Set c = Sh2Range.Find( _
    what:=Sh1cell, LookIn:=xlValues)
    If c Is Nothing Then
    Sh1cell.Interior.ColorIndex = 3
    Sh1cell.Offset(0, 1).Interior.ColorIndex = 3
    Else
    If Sh1cell.Offset(0, 1) <> c.Offset(0, 1) Then
    Sh1cell.Interior.ColorIndex = 6
    Sh1cell.Offset(0, 1).Interior.ColorIndex = 6
    End If
    End If
    Next Sh1cell
    'compare sheet 2 with sheet 1
    For Each Sh2cell In Sh2Range
    Set c = Sh1Range.Find( _
    what:=Sh2cell, LookIn:=xlValues)
    If c Is Nothing Then
    Sh2cell.Interior.ColorIndex = 3
    Sh2cell.Offset(0, 1).Interior.ColorIndex = 3
    Else
    If Sh2cell.Offset(0, 1) <> c.Offset(0, 1) Then
    Sh2cell.Interior.ColorIndex = 6
    Sh2cell.Offset(0, 1).Interior.ColorIndex = 6
    End If
    End If
    Next Sh2cell
    
    End Sub
    
    
    Sub CheckRev_v2()
    '====================
    'Originating author: Joel from "microsoft.public.excel.programming" 10.09.07
    '====================
    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
    Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Sh1Range = .Range("A1:A" & Sh1LastRow)
    End With
    With Sheets("Sheet2")
    Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Set Sh2Range = .Range("A1:A" & Sh2LastRow)
    End With
    
    'compare sheet 1 with sheet 2
    For Each Sh1cell In Sh1Range
    Set c = Sh2Range.Find( _
    what:=Sh1cell, LookIn:=xlValues)
    If c Is Nothing Then
    Sh1cell.Offset(0, 2).Value = "No Match Found!"
    Sh1cell.Offset(0, 2).Font.Color = -16776961
    Sh1cell.Offset(0, 2).Font.Bold = True
    'Sh1cell.Offset(0, 3).Value = Sh1cell.Offset(0, 1).Value 'enters Revlevel
    Else
    If Sh1cell.Offset(0, 1) <> c.Offset(0, 1) Then
    Sh1cell.Offset(0, 2).Font.Italic = True
    Sh1cell.Offset(0, 2).ColumnWidth = 25
    Sh1cell.Offset(0, 2).Value = "Revision Level Change!"
    End If
    End If
    Next Sh1cell
    'compare sheet 2 with sheet 1
    For Each Sh2cell In Sh2Range
    Set c = Sh1Range.Find( _
    what:=Sh2cell, LookIn:=xlValues)
    If c Is Nothing Then
    Sh2cell.Offset(0, 2).Value = "No Match Found!"
    Sh2cell.Offset(0, 2).Font.Color = -16776961
    Sh2cell.Offset(0, 2).Font.Bold = True
    'Sh2cell.Offset(0, 3).Value = Sh2cell.Offset(0, 1).Value 'enters Revlevel
    Else
    If Sh2cell.Offset(0, 1) <> c.Offset(0, 1) Then
    Sh2cell.Offset(0, 2).Font.Italic = True
    Sh2cell.Offset(0, 2).ColumnWidth = 25
    Sh2cell.Offset(0, 2).Value = "Revision Level Change!"
    End If
    End If
    Next Sh2cell
    Application.ScreenUpdating = True
    End Sub
    '=====================
    
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Thursday, October 2, 2014 9:51 PM
  • Hi o2code,

    I want to know : if each value in sheet1 column B is found anywhere in sheet3 column D

    To acheve this goal, I suggest that you loop the cells in Column B and check whether it containd in Column D.

    Here is a sample for your reference:

    Sub test()
    
    Set SourceRange = Application.Intersect(Range("A:A"), ActiveSheet.UsedRange)
    Set TargetRange = Application.Intersect(Range("D:D"), ActiveSheet.UsedRange)
    Dim contained As Boolean
    contained = True
    For Each aCell In SourceRange.Cells
    
        If Not IsEmpty(aCell.Value) Then
            Set c = TargetRange.Find(aCell.Value, LookIn:=xlValues)
            If c Is Nothing Then
                contained = False
                Exit For
            End If
        End If
    Next aCell
    
    Debug.Print "Column A is contianed in Column D:" & contained
    End Sub
    
    
    

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 7, 2014 12:50 PM
    Moderator