How to Compare Two Tables for Differences with Excel VBA (3 Methods)

In this article, I’ll show you how you can compare two tables for differences using VBA in Excel. You’ll learn to compare two tables by finding out the common elements between the two tables, calculating the differences between two columns of the two tables, and finally calculating the growth percentage of one column of one table with respect to the one of the other table.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Suitable Methods to Compare Two Tables for Differences with Excel VBA

Here we’ve got an Excel workbook with two tables called Table1 and Table2 in two worksheets called Sheet1 and Sheet2 respectively.

Table1 contains the sales record of some products for the month of January.

Table 1 to Compare Two Tables for Differences with Excel VBA

And Table2 in Sheet2. It contains the sales record for the month of February.

Table 2 to Compare Two Tables for Differences with Excel VBA

Our objective today is to compare the differences between these two tables using Visual Basic of Applications (VBA).


1. Compare Two Tables by Finding Out the Common Elements in a Column with Excel VBA

First of all, we’ll try to find out the product names that are common to both the table. We’ll sort out this in a new worksheet called Sheet3.

The VBA code for this purpose will be:

⧭ VBA Code:

Sub Common_Elements_between_Two_Tables()

Table1_Sheet = "Sheet1"
Table1 = "Table1"

Table2_Sheet = "Sheet2"
Table2 = "Table2"

Compare_Column = "Product Name"

Output_Sheet = "Sheet3"
Output_Cell = "B4"

Set Range1 = Worksheets(Table1_Sheet).Range(Table1 + "[" + Compare_Column + "]")
Set Range2 = Worksheets(Table2_Sheet).Range(Table2 + "[" + Compare_Column + "]")
Set Output_Range = Worksheets(Output_Sheet).Range(Output_Cell)

Count = 1

For i = 1 To Range1.Rows.Count
    For j = 1 To Range2.Rows.Count
        If Range1.Cells(i, 1) = Range2.Cells(j, 1) Then
            Output_Range.Cells(Count, 1) = Range1.Cells(i, 1)
            Count = Count + 1
            Exit For
        End If
    Next j
Next i

End Sub

VBA Code to Compare Two Tables for Differences with Excel VBA

⧭ Output:

Run the code. It’ll sort out the common products between the two tables in a range starting from cell B4 of Sheet3.

⧭ Notes:

Create the worksheet Sheet3 (Or any other worksheet you like) before running the code. Otherwise, it’ll show an error.

And the first 8 lines of the code contains the inputs to the code (Worksheet names, table names, column name, etc.). Don’t forget to change those according to your need before running the code.

Read More: How to Make a Comparison Table in Excel (2 Methods)


2. Compare Two Tables by Calculating the Differences Between Two Columns with Excel VBA

This time, first, we’ll try to find out the product names that are common to both tables. Then we’ll calculate the differences in total sales of these common products between the tables.

We’ll extract this in another new worksheet called Sheet4.

The VBA code for this purpose will be:

⧭ VBA Code:

Sub Differences_between_Two_Tables()

Table1_Sheet = "Sheet1"
Table1 = "Table1"

Table2_Sheet = "Sheet2"
Table2 = "Table2"

Compare_Column = "Product Name"
Difference_Column = "Total Sales"

Output_Sheet = "Sheet4"
Output_Cell = "B4"

Set Range11 = Worksheets(Table1_Sheet).Range(Table1 + "[" + Compare_Column + "]")
Set Range21 = Worksheets(Table2_Sheet).Range(Table2 + "[" + Compare_Column + "]")

Set Range12 = Worksheets(Table1_Sheet).Range(Table1 + "[" + Difference_Column + "]")
Set Range22 = Worksheets(Table2_Sheet).Range(Table2 + "[" + Difference_Column + "]")

Set Output_Range = Worksheets(Output_Sheet).Range(Output_Cell)

Count = 1

For i = 1 To Range11.Rows.Count
    For j = 1 To Range21.Rows.Count
        If Range11.Cells(i, 1) = Range21.Cells(j, 1) Then
            Output_Range.Cells(Count, 1) = Range11.Cells(i, 1)
            Output_Range.Cells(Count, 2) = Range22.Cells(j, 1) - Range12.Cells(i, 1)
            Count = Count + 1
            Exit For
        End If
    Next j
Next i

End Sub

VBA Code to Compare Two Tables for Differences with Excel VBA

⧭ Output:

Run the code. It’ll sort out the common products between the two tables and the differences in their sales in a range starting from cell B4 of Sheet4.

⧭ Notes:

Again, first, create the worksheet Sheet4 (Or any other worksheet you like) and then run the code. Also, change the inputs according to your needs in the first 9 lines of the code.

Read More: Compare Two Tables and Highlight Differences in Excel (4 Methods)


Similar Readings


3. Compare Two Tables by Calculating the Growth Percentages of One Column with Respect to Another with Excel VBA

Finally, we’ll extract the product names that are common to both tables and then calculate the growth percentages of the total sales of Table2 with respect to that of Table1.

We’ll execute this in a new worksheet called Sheet5.

The VBA code for this purpose will be:

⧭ VBA Code:

Sub Growth_Percentage_of_Two_Tables()

Table1_Sheet = "Sheet1"
Table1 = "Table1"

Table2_Sheet = "Sheet2"
Table2 = "Table2"

Compare_Column = "Product Name"
Percentage_Column = "Total Sales"

Output_Sheet = "Sheet5"
Output_Cell = "B4"

Set Range11 = Worksheets(Table1_Sheet).Range(Table1 + "[" + Compare_Column + "]")
Set Range21 = Worksheets(Table2_Sheet).Range(Table2 + "[" + Compare_Column + "]")

Set Range12 = Worksheets(Table1_Sheet).Range(Table1 + "[" + Percentage_Column + "]")
Set Range22 = Worksheets(Table2_Sheet).Range(Table2 + "[" + Percentage_Column + "]")

Set Output_Range = Worksheets(Output_Sheet).Range(Output_Cell)

Count = 1

For i = 1 To Range11.Rows.Count
    For j = 1 To Range21.Rows.Count
        If Range11.Cells(i, 1) = Range21.Cells(j, 1) Then
            Output_Range.Cells(Count, 1) = Range11.Cells(i, 1)
            Output_Range.Cells(Count, 2) = (Range22.Cells(j, 1) - Range12.Cells(i, 1)) / Range12.Cells(i, 1)
            Output_Range.Cells(Count, 2) = Format(Output_Range.Cells(Count, 2), "0.00%")
            Count = Count + 1
            Exit For
        End If
    Next j
Next i

End Sub

VBA Code to Compare Two Tables for Differences in Excel

⧭ Output:

Run the code. It’ll sort out the common products between the two tables and the growth percentages in their sales in a range starting from cell B4 of Sheet5.

⧭ Notes:

Again, first, create the worksheet Sheet5 (Or any other worksheet you like) and then run the code. Also, change the inputs according to your needs in the first 9 lines of the code.

Read More: How to Calculate Percent Frequency Distribution in Excel (2 Methods)


Things to Remember

Here I’ve focused on comparing the differences between the two tables only. If you are interested in more details about the VBA table, you can visit this link.


Conclusion

So, these are the ways to compare two tables in Excel VBA for differences. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo