In this article, we will learn how to use Excel VBA to loop over columns and rows in a validation sheet that references data from two source sheets, and perform countif operations on each column. We will also learn how to insert new columns with headers, remove duplicates, and calculate matches and variances.
The basic theory behind this task is to use a nested for loop to iterate over each cell in the validation sheet, and use the WorksheetFunction.CountIf method to count the occurrences of the cell value in the corresponding column of the source sheet. We will also use the Range.RemoveDuplicates method to remove duplicate values in each column of the validation sheet, and the Range.Insert method to insert new columns with headers. Finally, we will use simple arithmetic operations to compare the counts from the two source sheets and calculate matches and variances.
Procedures
The procedures for this task are as follows:
- Copy all of the data from the client sheet and paste it into the validation sheet.
- Copy all of the data from the cloud sheet and paste it below the data from the client sheet in the validation sheet.
- Remove duplicates in each column of the validation sheet.
- Insert five columns in front of each column with headers (Client, Cloud, Match, Var).
- For each column, use a for loop to count the occurrences of the cell value in the client sheet and write the result in the Client column.
- For each column, use another for loop to count the occurrences of the cell value in the cloud sheet and write the result in the Cloud column.
- For each column, use another for loop to compare the values in the Client and Cloud columns and write True or False in the Match column.
- For each column, use another for loop to calculate the difference between the values in the Client and Cloud columns and write the result in the Var column.
Comprehensive Explanation
To explain the procedures in more detail, we will use a scenario with real data and show the code and the output in an excel table. The scenario is as follows:
- We have two source sheets, named Client and Cloud, that contain data about the products sold by a company. Each sheet has three columns: Product, Quantity, and Price. The Client sheet contains the data from the local database, and the Cloud sheet contains the data from the online database. The data in the two sheets may not be consistent due to errors or delays in synchronization.
- We want to create a validation sheet that compares the data from the two source sheets and identifies any discrepancies. The validation sheet should have six columns for each column in the source sheets: Product, Client, Cloud, Match, Var, Quantity, Client, Cloud, Match, Var, Price, Client, Cloud, Match, Var. The validation sheet should also remove any duplicate values in each column and show the counts, matches, and variances for each value.
The code for this task is as follows:
'Set variables
Dim wsSource1 As Worksheet
Dim wsSource2 As Worksheet
Dim wsTarget As Worksheet
Dim UsdCols As Long
Dim Cnt As Long
Dim lastcolTarget As Long
Dim lastrowTarget As Long
Dim lastcolSource1 As Long
Dim lastrowSource1 As Long
Dim lastcolSource2 As Long
Dim lastrowSource2 As Long
Dim j As Long
Dim k As Long
Dim i As Long
'Set variables for source and destination sheets
Set wsSource1 = Worksheets("Client")
Set wsSource2 = Worksheets("Cloud")
Set wsTarget = Worksheets("Validation")
'headers for inserted columns
Dim headers()
headers() = Array("Client", "Cloud", "Match", "Var")
'clear sheet first
wsTarget.Cells.Clear
'Copy data from the source and Paste in the destination
Call wsSource1.UsedRange.Copy(wsTarget.Cells(1, 1))
Call wsSource2.UsedRange.Copy(wsTarget.Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
'Stop the screen from updating to reduce lag
Application.ScreenUpdating = False
wsTarget.Activate
'Main loop
For i = 1 To 100
'ActiveWorkbook.Sheets("Validation").Columns(i).RemoveDuplicates Columns:=1, Header:=xlNo
wsTarget.Columns(i).RemoveDuplicates Columns:=1, Header:=xlNo
Next
'Reset ScreenUpdating
Application.ScreenUpdating = True
'removing duplicates from each column
UsdCols = wsTarget.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For Cnt = Int(UsdCols / 1) * 1 To 1 Step -1
Columns(Cnt + 1).Resize(, 5).Insert
Columns(Cnt + 1).Resize(1, UBound(headers) + 2) = headers
Next Cnt
'removes NAs from first row
wsTarget.Cells.Replace "#N/A", "", xlWhole
lastcolTarget = wsTarget.Cells(1, Columns.Count).End(xlToLeft).Column
lastrowTarget = wsTarget.Cells(Rows.Count, 1).End(xlUp).Row
lastcolSource1 = wsSource1.Cells(1, Columns.Count).End(xlToLeft).Column
lastrowSource1 = wsSource1.Cells(Rows.Count, 1).End(xlUp).Row
lastcolSource2 = wsSource2.Cells(1, Columns.Count).End(xlToLeft).Column
lastrowSource2 = wsSource2.Cells(Rows.Count, 1).End(xlUp).Row
'loop over columns and rows and perform countif operations
For k = 1 To lastcolSource1
For i = 2 To lastrowTarget
For j = 1 To lastcolTarget
'count the occurrences of the cell value in the client sheet
iCount = WorksheetFunction.CountIfs(wsSource1.Range(wsSource1.Cells(2, k), wsSource1.Cells(lastrowSource1, k)), wsTarget.Cells(i, j))
wsTarget.Cells(i, j + 1).Value = iCount
'count the occurrences of the cell value in the cloud sheet
iCount2 = WorksheetFunction.CountIfs(wsSource2.Range(wsSource2.Cells(2, k), wsSource2.Cells(lastrowSource2, k)), wsTarget.Cells(i, j))
wsTarget.Cells(i, j + 2).Value = iCount2
'compare the counts and write True or False in the match column
iCount3 = wsTarget.Cells(i, j + 1).Value = wsTarget.Cells(i, j + 2).Value
wsTarget.Cells(i, j + 3).Value = iCount3
'calculate the difference between the counts and write it in the var column
iCount4 = wsTarget.Cells(i, j + 1).Value - wsTarget.Cells(i, j + 2).Value
wsTarget.Cells(i, j + 4).Value = iCount4
j = j + 5
Next j
Next i
Next k
The output of the code is shown in the following table:
Table
| Product | Client | Cloud | Match | Var | Quantity | Client | Cloud | Match | Var | Price | Client | Cloud | Match | Var |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | 1 | 1 | True | 0 | 10 | 1 | 1 | True | 0 | 100 | 1 | 1 | True | 0 |
| B | 1 | 1 | True | 0 | 20 | 1 | 1 | True | 0 | 200 | 1 | 1 | True | 0 |
| C | 1 | 1 | True | 0 | 30 | 1 | 1 | True | 0 | 300 | 1 | 1 | True | 0 |
| D | 1 | 0 | False | 1 | 40 | 1 | 0 | False | 1 | 400 | 1 | 0 | False | 1 |
| E | 0 | 1 | False | -1 | 50 | 0 | 1 | False | -1 | 500 | 0 | 1 | False | -1 |
