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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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. And Table2 in Sheet2. It contains the sales record for the month of February. 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`````` ⧭ 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: Excel VBA to Create Table from Range

### 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`````` ⧭ 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.

### 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`````` ⧭ 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.

## 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 ExcelDemy.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. 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 Advanced Excel Exercises with Solutions PDF  