skyzzn
02-18-2019, 09:32 AM
Hi guys,
I hope youre all doing well! So I want to compare two sheets and check if the entries of sheet 1 exist in sheet 2 and vice versa. The first challenge is that one "entry" is identified by all columns of one row. The second challenge is that there is no order of the rows.
So i started a VBA macro which highlights the cells in sheet 2 which are not found in sheet 1.
The code works fine when I have a primary key in the first column. But when I have different entries which only match in the first column, my code is not working properly. I somehow need to check the combination of all columns. So I need to concat the value of each column of a row and compare them (sheet 1 and sheet 2) or something similiar.
Im really new to VBA so please go easy on me! Here is my code:
Sub CompareSheets(shtSheet1 As String, shtSheet2 As String)
Dim c As Integer, j As Integer, i As Integer, mydiffs As Integer, cnt1 As Integer, cnt2 As Integer
Dim noexist As Integer
'count the rows of each sheet
cnt2 = Worksheets("Sheet2").Cells.SpecialCells(xlCellTypeLastCell).Row
cnt1 = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
'For each cell in sheet2 that is not the same in Sheet1, color it yellow
For i = 1 To cnt2
For j = 1 To cnt1
If ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(j, 1).Value Then
For c = 2 To 22
If Not ActiveWorkbook.Worksheets(shtSheet2).Cells(i, c).Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(j, c).Value Then
ActiveWorkbook.Worksheets(shtSheet2).Cells(i, c).Interior.Color = vbYellow
mydiffs = mydiffs + 1
End If
Next
Exit For
End If
'when sheet2 has a new entry (meaning value is not in sheet1), color it red
If j = cnt1 Then
ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Interior.Color = vbRed
noexist = noexist + 1
End If
Next
Next
ActiveWorkbook.Sheets(shtSheet2).Select
End Sub
I really dont need the highlighting, I just want to see which row is different in sheet 2 by e.g. new column called Expected Results. Heres an example file: 23765
When you execute my Code, youll see I get a much different result than I need.
If I didnt explain my problem well enough, please feel free to ask. I would appreciate any help! Thank you in advance.
Best regards
Alex
I hope youre all doing well! So I want to compare two sheets and check if the entries of sheet 1 exist in sheet 2 and vice versa. The first challenge is that one "entry" is identified by all columns of one row. The second challenge is that there is no order of the rows.
So i started a VBA macro which highlights the cells in sheet 2 which are not found in sheet 1.
The code works fine when I have a primary key in the first column. But when I have different entries which only match in the first column, my code is not working properly. I somehow need to check the combination of all columns. So I need to concat the value of each column of a row and compare them (sheet 1 and sheet 2) or something similiar.
Im really new to VBA so please go easy on me! Here is my code:
Sub CompareSheets(shtSheet1 As String, shtSheet2 As String)
Dim c As Integer, j As Integer, i As Integer, mydiffs As Integer, cnt1 As Integer, cnt2 As Integer
Dim noexist As Integer
'count the rows of each sheet
cnt2 = Worksheets("Sheet2").Cells.SpecialCells(xlCellTypeLastCell).Row
cnt1 = Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
'For each cell in sheet2 that is not the same in Sheet1, color it yellow
For i = 1 To cnt2
For j = 1 To cnt1
If ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(j, 1).Value Then
For c = 2 To 22
If Not ActiveWorkbook.Worksheets(shtSheet2).Cells(i, c).Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(j, c).Value Then
ActiveWorkbook.Worksheets(shtSheet2).Cells(i, c).Interior.Color = vbYellow
mydiffs = mydiffs + 1
End If
Next
Exit For
End If
'when sheet2 has a new entry (meaning value is not in sheet1), color it red
If j = cnt1 Then
ActiveWorkbook.Worksheets(shtSheet2).Cells(i, 1).Interior.Color = vbRed
noexist = noexist + 1
End If
Next
Next
ActiveWorkbook.Sheets(shtSheet2).Select
End Sub
I really dont need the highlighting, I just want to see which row is different in sheet 2 by e.g. new column called Expected Results. Heres an example file: 23765
When you execute my Code, youll see I get a much different result than I need.
If I didnt explain my problem well enough, please feel free to ask. I would appreciate any help! Thank you in advance.
Best regards
Alex