compare two columns

[naresh55]

Hi All,

How to create VBA coding for I'm having data to compare in sheet 1 column B and C, if any mis matched value found it should be mention in sheet 2 as mention below

Input Sheet 1:

A	B	C
Customer Name	ABC	ABC
Part Number	2342	2342
Qty	50	100
Delivery Date	31.12.2016	31.12.2016
Net Price	9000	10000

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Mismatch value to be publish n sheet 2 mention formate
Output in Sheet 2:

A	B	C	D	E	F
s NO	Customer Name	Part Number	Qty	Delivery Date	Net Price

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

100 10000

 

[portews]

Assuming that the entire Sheet1 is formatted this way, with 5 rows of customer info and you want a row number to verify, try this:

Sub CompareCustomers()
[COLOR=#00ff00]'http://www.mrexcel.com/forum/excel-questions/981155-compare-two-columns.html[/COLOR]
Dim i As Integer
Dim j As Integer
Dim LastRowSht2 As Integer
[COLOR=#00ff00]'for each customer[/COLOR]
For i = 1 To Sheets(1).Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row Step 5
[COLOR=#00ff00]    'find the last row in Sheet 2[/COLOR]
    LastRowSht2 = Sheets(2).Cells(Sheets(2).Rows.Count, "A").End(xlUp).Row + 1
[COLOR=#00ff00]    'loop thru the customer information[/COLOR]
    For j = 0 To 4
[COLOR=#00ff00]        'if cells in B don't match the adjacent cells in C[/COLOR]
        If Not Sheets(1).Cells(i + j, 2) = Sheets(1).Cells(i + j, 3) Then
[COLOR=#00ff00]            'print the customer row number in the A column[/COLOR]
            Sheets(2).Cells(LastRowSht2, 1) = i
[COLOR=#00ff00]            'print the information in C that does not match in the appropriate column[/COLOR]
            Sheets(2).Cells(LastRowSht2, j + 2) = Sheets(1).Cells(i + j, 2)
        End If
    Next
Next
End Sub