none
VBA code for comparison RRS feed

  • Question

  • In a sheet I have following column name

    Table1                               Compare Field                    Table2

    Field Name  Datatype Reg       Result              Field Name  Datatype Reg

    Akshay          int          Yes             True                xyz          flot       no  

    Compare Button

    xyz              int             No              False              Akshay    int     Yes

    I have to compare table1 all three column data with table2 all three colum data. Table2 data can be present at any row I have to search and compare it.

    If all the column data matches I have to print status True in Compare Field Column after Table1 data

    Code will start executing after clicking on Compare Button

    Friday, September 15, 2017 7:13 AM

All replies

  • Hi Akshay Chavan 07,

    You could try and adjust below code for your need.

    Private Sub CommandButton1_Click()
    
    Dim tbl1Rng As Range
    
    Dim tbl2Rng As Range
    
    Set tbl1Rng = ActiveSheet.ListObjects(1).Range
    
    Set tbl2Rng = ActiveSheet.ListObjects(2).Range
    
    For i = 2 To tbl1Rng.Rows.Count
    
    name1 = tbl1Rng.Cells(i, 1)
    
    type1 = tbl1Rng.Cells(i, 2)
    
    reg1 = tbl1Rng.Cells(i, 3)
    
    For j = 2 To tbl2Rng.Rows.Count
    
    name2 = tbl2Rng.Cells(j, 1)
    
    type2 = tbl2Rng.Cells(j, 2)
    
    reg2 = tbl2Rng.Cells(j, 3)
    
    If name1 = name2 And type1 = type2 And reg1 = reg2 Then
    
            tbl1Rng.Cells(i, 1).Offset(0, 3) = "True"
    
            GoTo NextRow
    
            End If
    
        Next j
    
    tbl1Rng.Cells(i, 1).Offset(0, 3) = "False"
    
    NextRow:
    
    Next i
    
    End Sub

    Best Regards,

    Terry

    Friday, September 15, 2017 9:52 AM
  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    No formulas, no VBA macro.
    Table2 can be sorted and arranged randomly
    and contain fewer or more names than Table1.
    Click on "Refresh All"
    http://www.mediafire.com/file/zyghdpa34z6kd1k/09_15_17a.xlsx

    Friday, September 15, 2017 4:43 PM
  • Thanks for reply

    Sub Button2_Click()
    Dim tbl1Rng As Range

    Dim tbl2Rng As Range
    Dim tbl3Rng As Range
    Dim name1, type1, reg1, name2, type2, reg2 As Variant




    Set tbl1Rng = ThisWorkbook.ActiveSheet.Range(Cells(1, 3), Cells(1, 3000))
    Set tbl2Rng = ThisWorkbook.ActiveSheet.Range(Cells(5, 3), Cells(5, 3000))


        For i = 3 To tbl1Rng.Count
        
         
            name1 = Cells(i, 1)
            type1 = Cells(i, 2)
            reg1 = Cells(i, 3)
        
            For j = 3 To tbl2Rng.Count
        
                name2 = Cells(j, 5)
                type2 = Cells(j, 6)
                reg2 = Cells(j, 7)
        
                If name1 = name2 And type1 = type2 And reg1 = reg2 Then
        
                    Cells(i, 4) = "True"
        
                GoTo NextRow
        
                End If
        
            Next j
            Cells(i, 4) = "False"
            'tbl1Rng.Cells(i, 1).Offset(0, 3) = "False"
        
    NextRow:
        
        Next i

    End Sub

     little bit modification to your code

    Saturday, September 16, 2017 8:48 AM
  • Hi Akshay Chavan 07,

    Thanks for sharing your solution. Has your original issue been solved?

    If it has, I would suggest you mark useful reply or your solution to close this thread.

    If not, please feel free to let know us know your current issue.

    Best Regards,

    Terry

    Monday, September 18, 2017 1:23 AM