Evaluate two columns for matching data

Question

I want to compare two columns and say whether they are equal or not.

There are 100 data in first column and 80 data in second column. Out of 100, 80 data match with the second column.

If second column data is not matching with first column insert blank cell/0 at second column. Then take same second column data and check it with first column next data.

For example:

C1    C2    
1    1    
2    2    
3    4    
4    5    
5    

In the above example, 1st two rows matches. Then while comparing 3rd row C1 with C2, there is no match. So insert blank cell or 0 at 3rd row C2, and move 3rd row C2 value 4 to next row C2. Then compare 4th row c1 and c2.

Final result

c1    c2    
1    1    
2    2    
3    0    
4    4    
5    5    

Answer 1

you can try this:

Public Sub MatchValues(rInputRange As Range)

    Dim rCell As Range

    'Initial checks
    If rInputRange.Columns.Count <> 2 Then
        MsgBox "Supply two columns as the input range"
        Exit Sub
    End If

    For Each rCell In rInputRange.Columns(1).Cells
        If rCell.Offset(0, 1) > rCell Then
            rCell.Offset(0, 1).Insert xlDown
            rCell.Offset(0, 1) = 0
        End If
    Next rCell

End Sub

You can supply a range or the current selection:

MatchValues range("A1:D2")

or

MatchRanges selection

Answer 2

In you VBA IDE go to the tools pulldown menu and selecte references. Select "Microstoft ActiveX data objects 2.8 Library.

Private Sub CommandButton1_Click()
    Dim rs As New ADODB.Recordset
    Dim ws As Excel.Worksheet
    Dim lrow As Long

        Set ws = Application.ActiveSheet

        'Add fields to your recordset for storing data.
        With rs
            .Fields.Append "Row", adInteger
            .Fields.Append "ColumnB", adInteger
            .Open
        End With

        lrow = 1

        'Loop through and record what column B
        Do While lrow <= ws.UsedRange.Rows.count

            If ws.Range("B" & lrow).Value <> "" Then
                rs.AddNew
                rs.Fields("Row").Value = lrow
                rs.Fields("ColumnB").Value = ws.Range("B" & lrow).Value
                rs.Update
            End If

            lrow = lrow + 1
        Loop

        'Now go through and rewrite column B if A was found in B
        lrow = 1
        Do While lrow <= ws.UsedRange.Rows.count

            If ws.Range("A" & lrow).Value <> "" Then
                rs.Filter = ""
                rs.Filter = "ColumnB='" & ws.Range("A" & lrow).Value & "'"
                'If we have a match, write C and D
                If rs.RecordCount > 0 Then
                    ws.Range("B" & lrow).Value = rs.Fields("ColumnB").Value
                Else
                    ws.Range("B" & lrow).Value = 0
                End If
            End If

            lrow = lrow + 1
        Loop
End Sub