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​

 

[etaf]

if you wanted VBA , cant help - but can be done with a formula and some helper columns
IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$11)/(Sheet1!$B$2:$B$11=0),ROWS(A$2:A2))),"")

or using filter - with NO helper columns - But i dont think 2019 version has the FILTER() function
=FILTER(A2:C6&" <- sheet1 sheet2->"&Sheet2!A2:C6,A2:A6&B2:B6&C2:C6<>Sheet2!A2:A6&Sheet2!B2:B6&Sheet2!C2:C6)

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Data1	Data2	Data3	Helper		sheet 1 extract			sheet 2 extract
2	1	a	a	1		3	c	v	3	a	c			3 <- sheet1 sheet2->3
3	2	b	b	1		5	e	d	5	e	e			5 <- sheet1 sheet2->5
4	3	c	v	0		0	0	0	0	0	0
5	4	d	c	1		0	0	0	0	0	0
6	5	e	d	0		0	0	0	0	0	0
7						0	0	0	0	0	0
8						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))),"")
I2:K8	I2	=IFERROR(INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW(Sheet2!$D$2:$D$11)/(Sheet2!$D$2:$D$11=0),ROWS(A$2:A2))),"")
N2:P3	N2	=FILTER(A2:C6&" <- sheet1 sheet2->"&Sheet2!A2:C6,A2:A6&B2:B6&C2:C6<>Sheet2!A2:A6&Sheet2!B2:B6&Sheet2!C2:C6)
D2:D6	D2	=COUNTIFS(Sheet2!$A$2:$A$6,A2,Sheet2!$B$2:$B$6,B2,Sheet2!$C$2:$C$6,C2)
Dynamic array formulas.

Book4
	A	B	C	D
1	Data1	Data2	Data3	Helper
2	1	a	a	1
3	2	b	b	1
4	3	a	c	0
5	4	d	c	1
6	5	e	e	0
Sheet2

Cell Formulas
Range	Formula
D2:D6	D2	=COUNTIFS(Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,B2,Sheet1!$C$2:$C$6,C2)

the dropbox link will be around a few days only

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Legacy 143009]

Try:

Sub Test()
  Dim myArr1 As Variant, myArr2 As Variant
  myArr1 = Worksheets("Sheet1").UsedRange
  myArr2 = Worksheets("Sheet2").UsedRange
  
  For r = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    For c = 1 To Worksheets("Sheet1").UsedRange.Columns.Count
      If myArr2(r, c) <> myArr1(r, c) Then
        Worksheets("Sheet2").Cells(r, c).Interior.ColorIndex = 3
      End If
    Next
  Next
End Sub

 

[umerhayyat]

Try:

Sub Test()
  Dim myArr1 As Variant, myArr2 As Variant
  myArr1 = Worksheets("Sheet1").UsedRange
  myArr2 = Worksheets("Sheet2").UsedRange
 
  For r = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    For c = 1 To Worksheets("Sheet1").UsedRange.Columns.Count
      If myArr2(r, c) <> myArr1(r, c) Then
        Worksheets("Sheet2").Cells(r, c).Interior.ColorIndex = 3
      End If
    Next
  Next
End Sub

it ended up with an error "run-time error 9 subscript out of range"

further its highlighting cells not the rows ... requirement is to compare rows of two sheets and get the differences

 

[umerhayyat]

if you wanted VBA , cant help - but can be done with a formula and some helper columns
IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$B$2:$B$11)/(Sheet1!$B$2:$B$11=0),ROWS(A$2:A2))),"")

or using filter - with NO helper columns - But i dont think 2019 version has the FILTER() function
=FILTER(A2:C6&" <- sheet1 sheet2->"&Sheet2!A2:C6,A2:A6&B2:B6&C2:C6<>Sheet2!A2:A6&Sheet2!B2:B6&Sheet2!C2:C6)

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Data1	Data2	Data3	Helper		sheet 1 extract			sheet 2 extract
2	1	a	a	1		3	c	v	3	a	c			3 <- sheet1 sheet2->3
3	2	b	b	1		5	e	d	5	e	e			5 <- sheet1 sheet2->5
4	3	c	v	0		0	0	0	0	0	0
5	4	d	c	1		0	0	0	0	0	0
6	5	e	d	0		0	0	0	0	0	0
7						0	0	0	0	0	0
8						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))),"")
I2:K8	I2	=IFERROR(INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW(Sheet2!$D$2:$D$11)/(Sheet2!$D$2:$D$11=0),ROWS(A$2:A2))),"")
N2:P3	N2	=FILTER(A2:C6&" <- sheet1 sheet2->"&Sheet2!A2:C6,A2:A6&B2:B6&C2:C6<>Sheet2!A2:A6&Sheet2!B2:B6&Sheet2!C2:C6)
D2:D6	D2	=COUNTIFS(Sheet2!$A$2:$A$6,A2,Sheet2!$B$2:$B$6,B2,Sheet2!$C$2:$C$6,C2)
Dynamic array formulas.

Book4
	A	B	C	D
1	Data1	Data2	Data3	Helper
2	1	a	a	1
3	2	b	b	1
4	3	a	c	0
5	4	d	c	1
6	5	e	e	0
Sheet2

Cell Formulas
Range	Formula
D2:D6	D2	=COUNTIFS(Sheet1!$A$2:$A$6,A2,Sheet1!$B$2:$B$6,B2,Sheet1!$C$2:$C$6,C2)

the dropbox link will be around a few days only

Dropbox - File Deleted - Simplify your life

www.dropbox.com

thanks for the response, you are right 2019 doesn't have filter ... tried other one but didn't work as well

 

[Legacy 143009]

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.

Based on your statement, the code considers both sheets consist of the same number of rows and columns.

 

[Legacy 143009]

Ok try:

Sub Test()
  For r = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    For c = 1 To Worksheets("Sheet1").UsedRange.Columns.Count
      If Worksheets("Sheet2").Cells(r, c) <> Worksheets("Sheet1").Cells(r, c) Then
        Worksheets("Sheet2").Cells(r, c).EntireRow.Interior.ColorIndex = 3
      End If
    Next
  Next
End Sub

 

[umerhayyat]

didn't work ... all rows got highlighted

Ok try:

Sub Test()
  For r = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
    For c = 1 To Worksheets("Sheet1").UsedRange.Columns.Count
      If Worksheets("Sheet2").Cells(r, c) <> Worksheets("Sheet1").Cells(r, c) Then
        Worksheets("Sheet2").Cells(r, c).EntireRow.Interior.ColorIndex = 3
      End If
    Next
  Next
End Sub

 

[Legacy 143009]

further its highlighting cells not the rows ... requirement is to compare rows of two sheets and get the differences

I thought you want rows instead of cells. Anyway, someone may help.

 

[umerhayyat]

I thought you want rows instead of cells. Anyway, someone may help.

yup rows need to be highlighted if not available in other sheet ... number of columns are same however rows are not