Macro to Compare two Spreadsheets row by row for each column
Attached macro enabled spreadsheet will do the compare of the Source and Target Spreadsheet for each row and all the listed columns.
Instructions to Use:
1) Do not delete or change the Sheet Name or File Name of the Macro File.
2) Fill in the Data in the Data Sheet like Source File Path, Source File Name, target File Path, Target File Name, Sheet Names (Source and Target)
3) Also enter the Start Row and Total Columns in the Sheet to Compare
4) Enter the total number of rows in the File
5) Enter the Header Row Number and Column Number from where the data begins.
6) Enter the column which will represent as the Key for Search.
Click on the Validate button to compare and give you the results.
Sub Validation()
DataSheetValidation
Housekeeping
End Sub
Sub Housekeeping()
Dim sfileName, tfileName, sFolderName, tFolderName As String
Dim sSheetName, tSheetName As String
Dim nCol As Integer, stRowNum As Integer
'Variables
'Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
sFolderName = Range("B4").Value
sfileName = Range("B5").Value
tFolderName = Range("B6").Value
tfileName = Range("B7").Value
nCol = Range("B10").Value
stRowNum = Range("B12").Value
Application.DisplayAlerts = False
Sheets(sSheetName).Select
ActiveWindow.SelectedSheets.Delete
Sheets(tSheetName).Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
closeStatus = MsgBox("Would you Like to Close the Source and Target Worksheets?", vbYesNo, "Close the File")
If closeStatus = 6 Then
Workbooks(sfileName).Close SaveChanges:=False
Workbooks(tfileName).Close SaveChanges:=False
End If
Sheets("Validation").Select
' Range = Range(Cell(1, 1), Cell(stRowNum, nCol + 1))
End Sub
Sub DataSheetValidation()
Dim sSheetName, tSheetName As String
Dim nCol As Integer, bColNum As String, shRowNum As Integer
Dim thRowNum As Integer, enCol As Integer, stRowNum As Integer, ttRowNum As Integer
Dim sRowCount As Integer, keyColName As String
Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
nCol = Range("B10").Value
bColNum = Range("B11").Value
shRowNum = Range("B13").Value
thRowNum = Range("B15").Value
stRowNum = Range("B12").Value
ttRowNum = Range("B14").Value
keyColName = Range("B16").Value
'MsgBox (WorksheetFunction.CountA(Range(Cells(17, 2), Cells(17, nCol + 1))))
If nCol <> WorksheetFunction.CountA(Range(Cells(17, 2), Cells(17, nCol + 1))) Then
Result = MsgBox("Number of Columns Entered is not Matching with Total Number of Column for Validation.", vbCritical, "Error Encountered")
Else
CheckIfFileOpen
End If
Windows("Validation.xlsm").Activate
Sheets(sSheetName).Select
With ActiveSheet
sRowCount = .Cells(.Rows.count, keyColName).End(xlUp).Row
End With
If stRowNum <> sRowCount Then
Result = MsgBox("Total Number of Rows entered for Source Data doesn't match with the Total Rows in the Source sheet for the Key Field Column")
Else
Compare
End If
End Sub
Sub CheckIfFileOpen()
Dim fileName As String
Dim sfileName, tfileName, sFolderName, tFolderName As String
'Variables
'Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sFolderName = Range("B4").Value
sfileName = Range("B5").Value
tFolderName = Range("B6").Value
tfileName = Range("B7").Value
'Source File Open
fileName = sFolderName & sfileName
'Call function to check if the file is open
If IsFileOpen(fileName) = False Then
'Insert actions to be performed on the closed file
Workbooks.Open fileName
Else
'The file is open or another error occurred
MsgBox fileName & " is already open."
End If
' Target File Open
fileName = tFolderName & tfileName
'Call function to check if the file is open
If IsFileOpen(fileName) = False Then
'Insert actions to be performed on the closed file
Workbooks.Open fileName
Else
'The file is open or another error occurred
MsgBox fileName & " is already open."
End If
MoveSheets
End Sub
Function IsFileOpen(fileName As String)
Dim fileNum As Integer
Dim errNum As Integer
'Allow all errors to happen
On Error Resume Next
fileNum = FreeFile()
'Try to open and close the file for input.
'Errors mean the file is already open
Open fileName For Input Lock Read As #fileNum
Close fileNum
'Get the error number
errNum = Err
'Do not allow errors to happen
On Error GoTo 0
'Check the Error Number
Select Case errNum
'errNum = 0 means no errors, therefore file closed
Case 0
IsFileOpen = False
'errNum = 70 means the file is already open
Case 70
IsFileOpen = True
'Something else went wrong
Case Else
IsFileOpen = errNum
End Select
End Function
Sub MoveSheets()
Dim sfileName, tfileName, sFolderName, tFolderName As String
Dim sSheetName, tSheetName As String
'Variables
Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sFolderName = Range("B4").Value
sfileName = Range("B5").Value
tFolderName = Range("B6").Value
tfileName = Range("B7").Value
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
Windows(sfileName).Activate
Sheets(sSheetName).Select
Sheets(sSheetName).Copy After:=Workbooks("Validation.xlsm").Sheets(1)
Windows(tfileName).Activate
Sheets(tSheetName).Select
Sheets(tSheetName).Copy After:=Workbooks("Validation.xlsm").Sheets(2)
End Sub
Sub Compare()
Dim sSheetName, tSheetName As String
Dim nCol As Integer, bColNum As Integer, shRowNum As Integer
Dim thRowNum As Integer, enCol As Integer, stRowNum As Integer, ttRowNum As Integer
Dim sRowCount As Integer, keyColName As String, tcolName As String, scolName As String
Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
nCol = Range("B10").Value
bColNum = Range("B11").Value
shRowNum = Range("B13").Value
thRowNum = Range("B15").Value
stRowNum = Range("B12").Value
ttRowNum = Range("B14").Value
keyColName = Range("B16").Value
tcolName = Range("B18").Value
scolName = Range("B17").Value
Sheets("Validation").Visible = True
'Copy the Header Rows to the Validation Sheet from the Source Sheet
Sheets(sSheetName).Select
Range(Cells(shRowNum, bColNum), Cells(shRowNum, nCol)).Select
Selection.Copy
Sheets("Validation").Select
Range("A1").Value = "Key Field Data"
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Start Comparing the Data for each row in Source Data for all the Columns listed with Target Data
Dim sValue As String, tValue As String, keyValue As String, valRow As Integer, valCol As Integer
Dim FoundCell As Range, tidRowNum As Integer
valRow = 2
valCol = 2
For r = shRowNum + 1 To stRowNum
'Get the Key Field Data
Sheets(sSheetName).Select
keyValue = Range(keyColName & r).Value
'Populate the Key Field Data in Validation Sheet
Sheets("Validation").Select
Cells(valRow, 1).Value = keyValue
'valCol = valCol + 1
'Identify the Row number in Target based on the Source Key Data
Sheets(tSheetName).Select
' Range("A1").Select
Set FoundCell = Range(tcolName & ":" & tcolName).Find(keyValue)
If Not FoundCell Is Nothing Then
tidRowNum = FoundCell.Row
'After the Row is Identified Compare Each Column Data and Record the Status.
x = tcolName
For C = 1 To nCol
Sheets("Datasheet").Select
scolName = Cells(17, C + 1).Value
tcolName = Cells(18, C + 1).Value
Sheets(sSheetName).Select
sValue = Range(scolName & r).Value
Sheets(tSheetName).Select
tValue = Range(tcolName & tidRowNum).Value
Sheets("Validation").Select
If sValue = tValue Then
Cells(valRow, C + 1).Value = "Matched"
Else
Cells(valRow, C + 1).Value = "Un Matched"
Rows(valRow).Interior.Color = vbYellow
End If
valCol = valCol + 1
Next C
Else
'MsgBox (WHAT_TO_FIND & " not found")
Sheets("Validation").Select
Cells(valRow, 2).Value = "No Data Found in Target Sheet for Key Data"
Rows(valRow).Interior.Color = vbRed
End If
valRow = valRow + 1
'valCol = 2
tcolName = x
Next r
End Sub
Instructions to Use:
1) Do not delete or change the Sheet Name or File Name of the Macro File.
2) Fill in the Data in the Data Sheet like Source File Path, Source File Name, target File Path, Target File Name, Sheet Names (Source and Target)
3) Also enter the Start Row and Total Columns in the Sheet to Compare
4) Enter the total number of rows in the File
5) Enter the Header Row Number and Column Number from where the data begins.
6) Enter the column which will represent as the Key for Search.
Click on the Validate button to compare and give you the results.
Sub Validation()
DataSheetValidation
Housekeeping
End Sub
Sub Housekeeping()
Dim sfileName, tfileName, sFolderName, tFolderName As String
Dim sSheetName, tSheetName As String
Dim nCol As Integer, stRowNum As Integer
'Variables
'Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
sFolderName = Range("B4").Value
sfileName = Range("B5").Value
tFolderName = Range("B6").Value
tfileName = Range("B7").Value
nCol = Range("B10").Value
stRowNum = Range("B12").Value
Application.DisplayAlerts = False
Sheets(sSheetName).Select
ActiveWindow.SelectedSheets.Delete
Sheets(tSheetName).Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
closeStatus = MsgBox("Would you Like to Close the Source and Target Worksheets?", vbYesNo, "Close the File")
If closeStatus = 6 Then
Workbooks(sfileName).Close SaveChanges:=False
Workbooks(tfileName).Close SaveChanges:=False
End If
Sheets("Validation").Select
' Range = Range(Cell(1, 1), Cell(stRowNum, nCol + 1))
End Sub
Sub DataSheetValidation()
Dim sSheetName, tSheetName As String
Dim nCol As Integer, bColNum As String, shRowNum As Integer
Dim thRowNum As Integer, enCol As Integer, stRowNum As Integer, ttRowNum As Integer
Dim sRowCount As Integer, keyColName As String
Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
nCol = Range("B10").Value
bColNum = Range("B11").Value
shRowNum = Range("B13").Value
thRowNum = Range("B15").Value
stRowNum = Range("B12").Value
ttRowNum = Range("B14").Value
keyColName = Range("B16").Value
'MsgBox (WorksheetFunction.CountA(Range(Cells(17, 2), Cells(17, nCol + 1))))
If nCol <> WorksheetFunction.CountA(Range(Cells(17, 2), Cells(17, nCol + 1))) Then
Result = MsgBox("Number of Columns Entered is not Matching with Total Number of Column for Validation.", vbCritical, "Error Encountered")
Else
CheckIfFileOpen
End If
Windows("Validation.xlsm").Activate
Sheets(sSheetName).Select
With ActiveSheet
sRowCount = .Cells(.Rows.count, keyColName).End(xlUp).Row
End With
If stRowNum <> sRowCount Then
Result = MsgBox("Total Number of Rows entered for Source Data doesn't match with the Total Rows in the Source sheet for the Key Field Column")
Else
Compare
End If
End Sub
Sub CheckIfFileOpen()
Dim fileName As String
Dim sfileName, tfileName, sFolderName, tFolderName As String
'Variables
'Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sFolderName = Range("B4").Value
sfileName = Range("B5").Value
tFolderName = Range("B6").Value
tfileName = Range("B7").Value
'Source File Open
fileName = sFolderName & sfileName
'Call function to check if the file is open
If IsFileOpen(fileName) = False Then
'Insert actions to be performed on the closed file
Workbooks.Open fileName
Else
'The file is open or another error occurred
MsgBox fileName & " is already open."
End If
' Target File Open
fileName = tFolderName & tfileName
'Call function to check if the file is open
If IsFileOpen(fileName) = False Then
'Insert actions to be performed on the closed file
Workbooks.Open fileName
Else
'The file is open or another error occurred
MsgBox fileName & " is already open."
End If
MoveSheets
End Sub
Function IsFileOpen(fileName As String)
Dim fileNum As Integer
Dim errNum As Integer
'Allow all errors to happen
On Error Resume Next
fileNum = FreeFile()
'Try to open and close the file for input.
'Errors mean the file is already open
Open fileName For Input Lock Read As #fileNum
Close fileNum
'Get the error number
errNum = Err
'Do not allow errors to happen
On Error GoTo 0
'Check the Error Number
Select Case errNum
'errNum = 0 means no errors, therefore file closed
Case 0
IsFileOpen = False
'errNum = 70 means the file is already open
Case 70
IsFileOpen = True
'Something else went wrong
Case Else
IsFileOpen = errNum
End Select
End Function
Sub MoveSheets()
Dim sfileName, tfileName, sFolderName, tFolderName As String
Dim sSheetName, tSheetName As String
'Variables
Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sFolderName = Range("B4").Value
sfileName = Range("B5").Value
tFolderName = Range("B6").Value
tfileName = Range("B7").Value
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
Windows(sfileName).Activate
Sheets(sSheetName).Select
Sheets(sSheetName).Copy After:=Workbooks("Validation.xlsm").Sheets(1)
Windows(tfileName).Activate
Sheets(tSheetName).Select
Sheets(tSheetName).Copy After:=Workbooks("Validation.xlsm").Sheets(2)
End Sub
Sub Compare()
Dim sSheetName, tSheetName As String
Dim nCol As Integer, bColNum As Integer, shRowNum As Integer
Dim thRowNum As Integer, enCol As Integer, stRowNum As Integer, ttRowNum As Integer
Dim sRowCount As Integer, keyColName As String, tcolName As String, scolName As String
Windows("Validation.xlsm").Activate
Sheets("DataSheet").Select
sSheetName = Range("B8").Value
tSheetName = Range("B9").Value
nCol = Range("B10").Value
bColNum = Range("B11").Value
shRowNum = Range("B13").Value
thRowNum = Range("B15").Value
stRowNum = Range("B12").Value
ttRowNum = Range("B14").Value
keyColName = Range("B16").Value
tcolName = Range("B18").Value
scolName = Range("B17").Value
Sheets("Validation").Visible = True
'Copy the Header Rows to the Validation Sheet from the Source Sheet
Sheets(sSheetName).Select
Range(Cells(shRowNum, bColNum), Cells(shRowNum, nCol)).Select
Selection.Copy
Sheets("Validation").Select
Range("A1").Value = "Key Field Data"
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Start Comparing the Data for each row in Source Data for all the Columns listed with Target Data
Dim sValue As String, tValue As String, keyValue As String, valRow As Integer, valCol As Integer
Dim FoundCell As Range, tidRowNum As Integer
valRow = 2
valCol = 2
For r = shRowNum + 1 To stRowNum
'Get the Key Field Data
Sheets(sSheetName).Select
keyValue = Range(keyColName & r).Value
'Populate the Key Field Data in Validation Sheet
Sheets("Validation").Select
Cells(valRow, 1).Value = keyValue
'valCol = valCol + 1
'Identify the Row number in Target based on the Source Key Data
Sheets(tSheetName).Select
' Range("A1").Select
Set FoundCell = Range(tcolName & ":" & tcolName).Find(keyValue)
If Not FoundCell Is Nothing Then
tidRowNum = FoundCell.Row
'After the Row is Identified Compare Each Column Data and Record the Status.
x = tcolName
For C = 1 To nCol
Sheets("Datasheet").Select
scolName = Cells(17, C + 1).Value
tcolName = Cells(18, C + 1).Value
Sheets(sSheetName).Select
sValue = Range(scolName & r).Value
Sheets(tSheetName).Select
tValue = Range(tcolName & tidRowNum).Value
Sheets("Validation").Select
If sValue = tValue Then
Cells(valRow, C + 1).Value = "Matched"
Else
Cells(valRow, C + 1).Value = "Un Matched"
Rows(valRow).Interior.Color = vbYellow
End If
valCol = valCol + 1
Next C
Else
'MsgBox (WHAT_TO_FIND & " not found")
Sheets("Validation").Select
Cells(valRow, 2).Value = "No Data Found in Target Sheet for Key Data"
Rows(valRow).Interior.Color = vbRed
End If
valRow = valRow + 1
'valCol = 2
tcolName = x
Next r
End Sub
Comments
Post a Comment