Sulprobil
Search…
sbCompareTwoLists
With a reconciliation you can ensure data integrity or consistency. A simple example is a comparison of two lists. There are many possible approaches to show which elements of list A are not in list B and vice versa.
In principle you can come up either with a realtime comparison or with a batch process to do this. The realtime reconciliation can be done with a worksheet formula (including a conditional format) or with a VBA function. As a rule of thumb you do not want its runtime to be unbearably long - let's say longer than 0.2 sec. The batch process can be a VBA subroutine or a pivot table. This might be your preferred choice if the reconciliation is quite complex or if it lasts quite long (minutes).
Here is an example for a conditional format (volatile worksheet formula):
An example for a batch process solution (a VBA subroutine):
Please read my Disclaimer.
1
Sub sbCompareTwoLists(rListA As Range, _
2
rListB As Range, _
3
rOutput As Range)
4
'Lists all elements of first list which are not in second one
5
'together with their row number in output area starting at
6
'rOutput.
7
'Reverse ("moc.LiborPlus.www") PB V0.2 25-Aug-2010
8
Dim objARows As Object, objBRows As Object
9
Dim i As Long, r As Range
10
11
'Clear output area - adjust if necessary
12
Range(rOutput, rOutput.Offset(4 + rListA.Count + _
13
rListB.Count, 1)).ClearContents
14
15
rOutput = "Elements of List A which are not in B": i = i + 1
16
rOutput.Offset(i, 0) = "Row #"
17
rOutput.Offset(i, 1) = "Value": i = i + 1
18
19
Set objARows = CreateObject("Scripting.Dictionary")
20
Set objBRows = CreateObject("Scripting.Dictionary")
21
'We store row numbers of all list elements in memory
22
For Each r In rListB
23
objBRows.Item(r.Text) = r.Row
24
Next r
25
For Each r In rListA
26
objARows.Item(r.Text) = r.Row
27
If objBRows.Item(r.Text) = 0 Then
28
'List element of A is not in B
29
rOutput.Offset(i, 0) = r.Row
30
rOutput.Offset(i, 1) = r.Text: i = i + 1
31
End If
32
Next r
33
rOutput.Offset(i, 0) = "Elements of List B which are not in A"
34
i = i + 1
35
rOutput.Offset(i, 0) = "Row #"
36
rOutput.Offset(i, 1) = "Value": i = i + 1
37
For Each r In rListB
38
If objARows.Item(r.Text) = 0 Then
39
'List element of B is not in A
40
rOutput.Offset(i, 0) = r.Row
41
rOutput.Offset(i, 1) = r.Text: i = i + 1
42
End If
43
Next r
44
45
Set objARows = Nothing
46
Set objBRows = Nothing
47
48
End Sub
49
50
Sub CommandButtonTest()
51
Call sbCompareTwoLists(Range("A2:A2001"), _
52
Range("B2:B2001"), Range("D9"))
53
End Sub
Copied!
sbCompareTwoLists.xlsm
107KB
Binary
sbCompareTwoLists.xlsm
Last modified 1yr ago
Copy link