Comparing the two excel sheets using MACRO

Comparing the two excel sheets using MACRO

Step1:- Create one sample text file with data like below

Sno|EmployeeId|EmployeeName
1|23423|krishnaRaju
2|32423|Ranja
3|45653|venkat

 Step2:- Create one sample text file with different data like below

Sno|EmployeeId|EmployeeName
1|23423|krishnaRaju
2|7456|Ranja
3|45653|yhy

Step3:- Create one excel file and click Alt+F11 to display the macro space the past the below code

Sub Button1_Click()

'Declare variables here
Dim sh As Long
Dim Row As Long, col As Long
Dim max_Row As Long, max_Column As Long
Dim DataSh1 As String, DataSh2 As String, FP1 As String, FP2 As String
Dim outcome, lastRow, lastCol, colNum As Integer
Dim RawInput As Variant, RawColumn As Variant
Dim WB1 As Workbook, WB2 As Workbook

'Receaving the input value to find which column has to convertion required
'RawInput = InputBox("Enter column number for which date format need to be changed")
'If IsNumeric(RawInput) Then
'        colNum = CInt(RawInput)
'    Else
'        outcome = MsgBox("Please Provide Correct Input", vbOKOnly, "OutPut")
'    Exit Sub
'End If

'select the text file to load the first sheet
FP1 = Application.GetOpenFilename("Text File,*.txt*", , "Please Select Source one Text Sheet")
LoadTextToWorkSheet FP1, "SQL SERVER", colNum
'select the second file to load the second sheet
FP2 = Application.GetOpenFilename("Text File,*.txt*", , "Please Select Source Two Text Sheet")
LoadTextToWorkSheet FP2, "Tera Data", colNum

    lastRow = Worksheets("SQL SERVER").Evaluate("COUNTA(A:A)")
    lastCol = Worksheets("SQL SERVER").Evaluate("COUNTA(1:1)")


RawInput = lastRow


RawColumn = lastCol
'InputBox ("Enter Number of Columns, Value should be greater than 0")

If IsNumeric(RawColumn) Then
    max_Column = CLng(RawColumn)
Else
    outcome = MsgBox("Please Enter Column Value Greater than 0 ", vbOKOnly, "OutPut")
End If

If Not IsNumeric(RawInput) Then
    outcome = MsgBox("Please Enter Value Greater than 1 ", vbOKOnly, "OutPut")
Else
    max_Row = CLng(RawInput)
    
If ThisWorkbook.Sheets.Count = 3 Then
    Dim WS As Worksheet
        Set WS = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        WS.Name = "Compared Data"
        ThisWorkbook.Sheets("Compared Data").Activate
    End If

    For Row = 1 To 1
        For col = 1 To max_Column
            ThisWorkbook.Sheets(4).Cells(Row, col).Value = ThisWorkbook.Sheets(2).Cells(Row, col).Value
        Next col
    Next Row

    For Row = 2 To max_Row
        For col = 1 To max_Column
            DataSh1 = CStr(ThisWorkbook.Sheets(2).Cells(Row, col))
            DataSh2 = CStr(ThisWorkbook.Sheets(3).Cells(Row, col))
            If DataSh1 <> DataSh2 Then
                ThisWorkbook.Sheets(2).Cells(Row, col).Interior.Color = vbYellow
                ThisWorkbook.Sheets(2).Cells(1, col).Interior.Color = vbCyan
                ThisWorkbook.Sheets(3).Cells(Row, col).Interior.Color = vbYellow
                ThisWorkbook.Sheets(3).Cells(1, col).Interior.Color = vbCyan
                ThisWorkbook.Sheets(4).Cells(Row, col) = "False"
                ThisWorkbook.Sheets(4).Cells(Row, col).Interior.Color = vbRed
                ThisWorkbook.Sheets(4).Cells(1, col).Interior.Color = vbCyan

            Else
                ThisWorkbook.Sheets(4).Cells(Row, col) = "True"
            End If

            If Row Mod 50000 = 0 And Row <> max_Row And col = max_Column Then
                ThisWorkbook.Save
                outcome = MsgBox("Completed " & Row & " Rows", vbOKOnly, "OutPut")
            End If

            If Row = max_Row And col = max_Column Then
                ThisWorkbook.Save
                outcome = MsgBox("Completed " & Row & " Rows", vbOKOnly, "OutPut")
            End If

        Next col
    Next Row
    ThisWorkbook.Sheets(4).Activate
    End If

End Sub
'Complete text file loading to excel sheet here
Sub LoadTextToWorkSheet(path As String, shName As String, col As Integer)
Dim WB As Workbook
Dim WS As Worksheet
Dim outcome As Integer
Set WS = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
WS.Name = shName
ThisWorkbook.Sheets(shName).Activate
Worksheets(shName).Columns(1).Select
Selection.NumberFormat = "@"
'Use to change the column format to custom date
'Worksheets(shName).Columns(col).Select
'Selection.NumberFormat = "yyyy-mm-dd hh:mm:ss"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & path, Destination:=Worksheets(shName).Range("A1"))
         .TextFileParseType = xlDelimited
         .TextFileOtherDelimiter = "|"
         .Refresh BackgroundQuery:=False
     End With
If shName = "Tera Data" Then
Set MR = Range("A1:BZ1")
For Each cell In MR
    If cell.Value = "LOADDATE" Then cell.EntireColumn.Delete
        Next

End If

outcome = MsgBox("Completed Loading", vbOKOnly, "Output")
End Sub

Step4:- Run the code first it will asks to select the first source file again it will ask one more text file to load the second sheet, once two sheets are loaded one more new sheet will create and display the matched and unmatched rows with different colors as below

Sno	EmployeeId	EmployeeName
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
TRUE	TRUE	FALSE

 

 

Read More »