How to compare two lists with VBA 

In the fast-paced realm of professional life, where data reigns supreme, the ability to swiftly compare and analyze information is a key skill. Whether you're reconciling financial records, validating databases, or identifying discrepancies in project timelines, the need to compare two lists of data is omnipresent. This is where Visual Basic for Applications (VBA) steps in as a powerhouse tool for Excel users, providing a rapid and efficient solution to streamline the comparison process.

Imagine having two extensive lists of data and the daunting task of identifying differences or similarities. This is a scenario encountered across diverse industries, from finance and project management to logistics and beyond. The ability to discern changes, additions, or omissions swiftly can be a game-changer, allowing professionals to make informed decisions and maintain data integrity.

This blog post will delve into the versatile applications of comparing two lists of data in the professional sphere and highlight how VBA, with its robust array handling capabilities, significantly accelerates the process. We'll explore common scenarios where list comparison is crucial, such as:

1. **Data Validation and Cleaning:** Ensure accuracy and consistency in datasets by identifying and rectifying discrepancies.

2. **Financial Reconciliation:** Streamline the reconciliation of financial statements or transaction records to identify discrepancies and ensure financial accuracy.

3. **Project Management:** Track changes in project timelines, resource allocation, or task completion by comparing project-related lists for effective decision-making.

4. **Inventory Management:** Keep inventory records up-to-date by quickly identifying discrepancies in stock levels, preventing errors in supply chain management.

5. **Customer Data Analysis:** Enhance customer relationship management by identifying changes or updates in customer databases for personalized and targeted interactions.

VBA's efficiency lies in its ability to handle large datasets by leveraging arrays, ensuring a rapid and seamless comparison process. As we navigate through practical examples and step-by-step guides, you'll discover how to harness the power of VBA to become a data comparison maestro in your professional endeavors. Let's embark on a journey to unlock the full potential of VBA for expeditious and accurate list comparisons.

1. Insert properties to make code faster, declare variables and define the size of the lists to be compared

The first part of the code inserts properties that will enable the code to run faster. The variables that will be hosting the lists and the outcome of the comparison are then defined.

• Sub comparelists() The subroutine is given a name

• Application.Screenupdating = False  Property ensures that no updating ( i.e. switching between sheets and rows) is not visible to the user, increasing speed

• Application.Calculation = xlManual Calculations are not performed automatically, as per normal excel set up. As a result the code runs faster.

• Dim A as variant, B as variant, C as variant, D as variant, E as variant. The Variant variables are defined They will be hosting the original lists and the outcome 

• Sheets("A").select. It is assumed that the file has a sheet called A contains the first list to be compared. The  sheet is selected

• Range("a650000").select. Cell A650000 is selected. It is assumed that the list contains less than 650000 rows. If this is not the case change the code to select a cell placed after the last row of the list

• Alstrw = Selection.end(xlUp).row. The End(xlup) property moves up from the selected cell to the first filled cells and identifies the row number. It places the last row number of the list in the variable Alstrw

• Range("ZZ1").select Cells ZZ1 is selected It is assumed that the list contains less than 702 columns (ZZ is column 702) If this is not the case change the code to select a cell placed after the last column

• Alstcol = range("ZZ1").End(xlToLeft).column. the End (xlleft) property moves left to the first filled cell and identifies the column number. It places the last column number of the list in the variable Alstcol

• A = Range(cells(1,1),cells(Alstrw,Alstcol)). The range delimited by cells A1 ( cells (1,1) ) and the cell with the coordiantes last row and last column is placed in the variant variable A 

• The same operations as the ones mentioned above are performed on sheet B in which the second list of data is contained.

Sub comparelists()

Application.Screenupdating = false 

Application.Calculation = xlManual

Dim A () as variant, B () as variant, C () as variant, D () as variant, E () as variant

Sheets("A").select

Range("a650000").select

Alstrw = Selection.end(xlUp).row

Range("ZZ1").select

Alstcol = range("ZZ1").End(xlToLeft).column

A = Range(cells(1,1),cells(Alstrw,Alstcol))

Sheets("B").select

Range("a650000").select

Blstrw = Selection.end(xlUp).row

Range("ZZ1").select

Blstcol = range("ZZ1").End(xlToLeft).column

B = Range(cells(1,1),cells(Blstrw,Blstcol)).select

2. Create the empty arrays to host the result of the comparison

Variant variables A and B contain the two lists to be compared. Additional 3 variables need to be created in order to host the results from the comparison. 

There will be 3 results 

• Items contained in both lists. They will be reported in variable C 

• Items present only in list A. They will be reported in list D

• Items present only in list B. They will be reported in list E  

The explanations for the code are the following:

• C = A  Variable A i smande equal to variable A This means that C now contains all the data from the first list As C will be used to host only items compared in both lists it needs to be emptied. All values contained in C are cancelled by looping through all elements of C and setting their value to Null ( "")

• For i = 1 to Alstrw Variable i loops through all the rows of C Given that C is equal to A the last row of C is equal to the last row of A, therefore the same variable ( Alstrw) is used

• For y = 1 to Alstcol Variable y loops through all the columns of C Given that C is equal to A the last column of C is equal to the last columns of A, therefore the same variable ( Alstcol) is used

• C ( i,y ) = "" The value of each element of C is placed to null ( "")

• next y Loop through the columns 

• Next i Loop through the rows

• C = D Variable D is made equal to variable C (currently empty following the previous loop)

• The same operations is performed on variable E which is originally created equal to B and then each value of E is made empty

C = A

For i = 1 to Alstrw

for y = 1 to Alstcol

C ( i,y ) = ""

next y

Next i

D = C

E = B

For i = 1 to Blstrw

for y = 1 to Blstcol

E ( i,y ) = ""

next y

Next i

3. Perform the comparison

The are now 5 variables

1. A Containing the data of the list reported in Sheet A

2. B Containing the data of the list reported in Sheet B

3. C Currently empty and will be populated with the data contained in A AND B

4. D Currently empty. Will be populated with the data contained ONLY in A

5. E Currently empty. Will be populated with the data contained ONLY in B

The following code populates variables C, D and E with the data described above

• Variables C and D are populated by comparing each element of A with all the elements of B

  • If an element of A is found in B then variable C is populated with the item 

  • If an element in A does not find a match in B  then variable D is populated. To know if no match has been found in B the variable flag is used. It is set to zero at the start and remains 0 if no match is identified. At the end of the loop through B if flag = 0 then the item has to be reported in D 

Crw = 1

Drw = 1 

flag = 0 

For i = 1 to Alstrw

For y = 1 to Blstrw

If A(i,1) = B(y,1) then

C(Crw,1) = A(i,1)

Crw = Crw +1

flag=1

Else:End if

Next y

If flag = 0 then

D(Drw,1) = A (i,1)

      Drw = Drw +1

Else

flag = 0

      End if

Next i

Variable E is populated by comparing each element of B with all the elements of A 

• If no elements of B are found in A then variable E is populated These are the items which are present only in B 

• The variable flag serves the purpose of identifying which items of B do not have a match in A and are then copied in E

Erw = 1

flag = 0

For i = 1 to Blstrw

For y = 1 to Alstrw

If B(i,1) = A(y,1) then

flag=1

Else:End if

Next y

If flag = 0 then

E(Erw,1) = B (i,1)

      Erw = Erw +1

Else

flag = 0

      End if

Next i

4. Report the outcomes in the excel file 

The spreadsheet containing the code has additional 3 sheets

• "Both" In which all the items contained in A AND B will be reported

• "Only A" In which the items contained exclusively in A are reported

• "Only B" In which the items contained exclusively in B are reported

The below code copies the information from the variables to the sheets mentioned above. The variables are then erased on order to free up the PC memory

The ScreenUpdating and Calculation properties are set back to their original mode

Sheets("Both").select

columns("A:Z").select

Selection.ClearContents

range(cells(1,1),cells(Crw,Alstcol)).Select

Selection.Value = C

Sheets("OnlyA").select

columns("A:Z").select

Selection.ClearContents

range(cells(1,1),cells(Drw,Alstcol)).Select

Selection.Value = D

Sheets("OnlyB").select

columns("A:Z").select

Selection.ClearContents

range(cells(1,1),cells(Drw,Blstcol)).Select

Selection.Value = E

Erase A 

Erase B 

Erase C

Erase D

Erase E

Application.Screenupdating = true

Application.Calculation = xlAutomatic

End Sub

The full code is reported below 

Sub comparelists()

Application.Screenupdating = false 

Application.Calculation = xlManual

Dim A () as variant, B () as variant, C () as variant, D () as variant, E () as variant

Sheets("A").select

Range("a650000").select

Alstrw = Selection.end(xlUp).row

Range("ZZ1").select

Alstcol = range("ZZ1").End(xlToLeft).column

A = Range(cells(1,1),cells(Alstrw,Alstcol))

Sheets("B").select

Range("a650000").select

Blstrw = Selection.end(xlUp).row

Range("ZZ1").select

Blstcol = range("ZZ1").End(xlToLeft).column

B = Range(cells(1,1),cells(Blstrw,Blstcol)).select

C = A

For i = 1 to Alstrw

for y = 1 to Alstcol

C ( i,y ) = ""

next y

Next i

D = C

E = B

For i = 1 to Blstrw

for y = 1 to Blstcol

E ( i,y ) = ""

next y

Next i

Crw = 1

Drw = 1 

flag = 0 

For i = 1 to Alstrw

For y = 1 to Blstrw

If A(i,1) = B(y,1) then

C(Crw,1) = A(i,1)

Crw = Crw +1

flag=1

Else:End if

Next y

If flag = 0 then

D(Drw,1) = A (i,1)

      Drw = Drw +1

Else

flag = 0

      End if

Next i

Erw = 1

flag = 0

For i = 1 to Blstrw

For y = 1 to Alstrw

If B(i,1) = A(y,1) then

flag=1

Else:End if

Next y

If flag = 0 then

E(Erw,1) = B (i,1)

      Erw = Erw +1

Else

flag = 0

      End if

Next i

Sheets("Both").select

columns("A:Z").select

Selection.ClearContents

range(cells(1,1),cells(Crw,Alstcol)).Select

Selection.Value = C

Sheets("OnlyA").select

columns("A:Z").select

Selection.ClearContents

range(cells(1,1),cells(Drw,Alstcol)).Select

Selection.Value = D

Sheets("OnlyB").select

columns("A:Z").select

Selection.ClearContents

range(cells(1,1),cells(Drw,Blstcol)).Select

Selection.Value = E

Erase A 

Erase B 

Erase C

Erase D

Erase E

Application.Screenupdating = true

Application.Calculation = xlAutomatic

End Sub