Excel Automation - Part 1

Automation Using Excel Macro

Most of us use the Excel for one reason or the other. with Excel we can automate all the manual work of data comparison.

Here is an example of how such a automation can be done.

Requirement.
1. There are two systems with Data1 and Data2
2. Because of some reason Data1 Accounts do not match with Data2 Accounts
3. The data of both the systems are provided in a separate excel sheets
4. Only the Accounts match in both sheets and rest of the data needs to be validated

Follow the below steps to accomplish the above requirement

=>Open a New Excel sheet
=>Copy the data of First System in Sheet "Data1"
=>Copy the data of Second System in Sheet "Data2"
=> Use Alt+F11 to open MS VS
=>Insert New Module
=>Create a new Sub by name Compare

The data I am using has a sample format. And the data does not remain same always, however a little change in the code can satisfy the requirement. The sample data here has the below format

Fig 1: Data Sheets

Fig 2: Data format in Data1 Sheet

Fig 3: Data format in Data2 Sheet

Use the below code (Modify the code as required)

Sub Compare()
    Dim iRowData1, iColumnData1 As Integer
    Dim iRowData2, iColumnData2 As Integer
    Dim MatchFlg As Integer
    iRowData1 = 2
    iColumnData1 = 1
    Sheets("Data1").Select
    While (Cells(iRowData1, iColumnData1) <> "")
    AccountData1 = Cells(iRowData1, iColumnData1)
    SsnData1 = Cells(iRowData1, iColumnData1 + 1)
    Sheets("Data2").Select
    iRowData2 = 2
    iColumnData2 = 1
    MatchFlg = 0
        While (Cells(iRowData2, iColumnData2) <> "")
        AccountData2 = Cells(iRowData2, iColumnData2)
        SsnData2 = Cells(iRowData2, iColumnData2 + 2)
            If AccountData1 = AccountData2 Then
                If SsnData1 = SsnData2 Then
                MatchFlg = 1
                GoTo NextRow
                End If
            End If
        iRowData2 = iRowData2 + 1
        Wend
NextRow:
        Sheets("Data1").Select
        If MatchFlg = 0 Then
        Cells(iRowData1, iColumnData1 + 3) = "No Match Found"
        Else
        Cells(iRowData1, iColumnData1 + 3) = "Match Found"
        End If
        iRowData1 = iRowData1 + 1
    Wend
End Sub


After the Macro run the Data1 Sheet would be having the following data

This code really helped me a lot. For me it saved around three days work and I had to compare around 35K records. The Macro would run for 5 min. 

For more Stay Tuned.....