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.
Download Practice Workbook
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.
Related Articles
- Excel VBA Code for Each Row in a Table
- Excel VBA: Insert Data into Table
- How to Create a Table with Headers Using Excel VBA
- Reference Table Column by Name with VBA in Excel
- How to Use Table Reference with Excel VBA
