comparing two worksheets

[umerhayyat]

Hi.

I'm wondering if anybody could help me with this or if it is possible.

I have two worksheets of data, each row is the same case on both sheets and the columns all contain the same variables I just need to know the differences between each row.

I'm looking for a VBA that could compare the two sheets row by row and output a list row by row of which cells differ?

I have tried to hunt something down but all I can find is VBA that would highlight the different cells which wont do for this task.

Any help would be greatly appreciated.

Many thanks,

Sheet1

1197326901

000207

002610175

700000​

Sheet

1197326901

000205

002610175

700000​

 

[Legacy 143009]

It must be based on something like an ID number on the first column. Otherwise there Is a probability that every row is different from one another.

 

[umerhayyat]

data has more columns however reconciliation shall be based on these four columns as required, if you can assist to do the matching based on these four columns

 

[Legacy 143009]

Sub Test()
  With Application
  For i = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    For j = 1 To Worksheets("Sheet2").UsedRange.Rows.Count
      If Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1) Then
        If Join(.Transpose(.Transpose(Worksheets("Sheet2").Rows(j).Value)), Chr(0)) <> Join(.Transpose(.Transpose(Worksheets("Sheet1").Rows(i).Value)), Chr(0)) Then
          Worksheets("Sheet2").Rows(j).EntireRow.Interior.ColorIndex = 3
        End If
      End If
    Next
  Next,
  End With
End Sub

This code considers column A is the identifier for both sheets.

 

[umerhayyat]

getting following error;

compile error:

syntax error

Sub Test()
  With Application
  For i = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    For j = 1 To Worksheets("Sheet2").UsedRange.Rows.Count
      If Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1) Then
        If Join(.Transpose(.Transpose(Worksheets("Sheet2").Rows(j).Value)), Chr(0)) <> Join(.Transpose(.Transpose(Worksheets("Sheet1").Rows(i).Value)), Chr(0)) Then
          Worksheets("Sheet2").Rows(j).EntireRow.Interior.ColorIndex = 3
        End If
      End If
    Next
  Next,
  End With
End Sub

This code considers column A is the identifier for both sheets.

etting

 

[etaf]

duplicated

 

[etaf]

do you want to extract the differences - as i should with Aggregate formulas - to show difference of sheet1 and sheet2 with a helper columns

VBA would not need the helper columns - and a better solution

But here is the conditional formatting

Book6
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Data1	Data2	Data3	Helper		sheet 1 extract				sheet 2 extract
2	1	a	a	1		3	c	v		3	a	c	0
3	2	b	b	1		5	e	d		5	e	e	0
4	3	c	v	0		0	0	0		0	0	0	0
5	4	d	c	1		0	0	0		0	0	0	0
6	5	e	d	0		0	0	0		0	0	0	0
7						0	0	0		0	0	0	0
8						0	0	0		0	0	0	0
Sheet1

Cell Formulas
Range	Formula
F2:H8	F2	=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$11)/(Sheet1!$D$2:$D$11=0),ROWS(A$2:A2))),"")
J2:M8	J2	=IFERROR(INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW(Sheet2!$D$2:$D$11)/(Sheet2!$D$2:$D$11=0),ROWS(A$2:A2))),"")
D2:D6	D2	=COUNTIFS(Sheet2!$A$2:$A$6,A2,Sheet2!$B$2:$B$6,B2,Sheet2!$C$2:$C$6,C2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D6	Expression	=COUNTIFS(Sheet2!$A$2:$A$6,$A2,Sheet2!$B$2:$B$6,$B2,Sheet2!$C$2:$C$6,$C2)=0	text	YES

 

[Legacy 143009]

There is a comma left after Next. Please try again:

Sub Test()
  With Application
  For i = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    For j = 1 To Worksheets("Sheet2").UsedRange.Rows.Count
      If Worksheets("Sheet2").Cells(j, 1) = Worksheets("Sheet1").Cells(i, 1) Then
        If Join(.Transpose(.Transpose(Worksheets("Sheet2").Rows(j).Value)), Chr(0)) <> Join(.Transpose(.Transpose(Worksheets("Sheet1").Rows(i).Value)), Chr(0)) Then
          Worksheets("Sheet2").Rows(j).EntireRow.Interior.ColorIndex = 3
        End If
      End If
    Next
  Next
  End With
End Sub