Hi,
This seems a bit of an old chestnut but please humour me ;)
My aim is to compare two arrays and identify values in List A which
don't appear in List B. And vice versa. My arrays are one dimensional
and contain names. The number of elements in each array is not fixed.
I've done some searching and there are two basic algorithms. The first
contains a double loop where each element of the inner loop is compared
to elements in the outer loop. At best it kicks out of the inner loop
when a match is found.
The second method seems to be more elegant but assumes the two arrays
have already been sorted. Basically the indeces of the two arrays are
incremented when an unmatched value is found. An example code can be
found below which works ... until you place a unique value as the last
element :(
For example, if I have:
ListA = Array("Andy", "Bob", "Cath", "Dexter")
ListB = Array("Andy", "Bob", "Cath")
BOTH "Cath" and "Dexter" are found to be unique! I switched the values
of list A to B and came up with the same poor result.
I'd appreciate it if someone could give this a fresh look and find the
logic error. I've been staring at it for hours :(
Many thanks in advance,
Andy
Script:
Option Explicit
Dim ListA ' To contain a sorted list of names, unknown size.
Dim ListB ' To contain a sorted list of names, unknown size.
Dim lngAIdx ' Index for list A.
Dim lngBIdx ' Index for list B.
Dim lngMaxAIdx ' Upper value of the A list index.
Dim lngMaxBIdx ' Upper value of the B list index.
Dim intComparison ' Result of comparing two strings
'
ListA = Array("Andy", "Bob", "Cath", "Dexter")
ListB = Array("Bob", "Cath", "Dexter")
lngAIdx = LBound(ListA)
lngBIdx = LBound(ListB)
lngMaxAIdx = UBound(ListA)
lngMaxBIdx = UBound(ListB)
'
Do While (lngAIdx < lngMaxAIdx And lngBIdx < lngMaxBIdx)
'Perform a case insensitive comparison.
intComparison = strComp(ListA(lngAIdx), ListB(lngBIdx), vbTextCompare)
If intComparison = -1 Then ' ListA(lngAIdx) < ListB(lngBIdx)
' Value in A list is not present in the B list.
WScript.Echo "Unique A Value: " & ListA(lngAIdx)
lngAIdx = lngAIdx + 1 ' Update the A list index.
ElseIf intComparison = 1 Then ' ListA(lngAIdx) > ListB(lngBIdx)
' Value in B list is not present in the A list.
WScript.Echo "Unique B Value: " & ListB(lngBIdx)
lngBIdx = lngBIdx + 1 ' Update the B list index.
Else ' intComparison = 0
' The value appears in both lists
lngAIdx = lngAIdx + 1
lngBIdx = lngBIdx + 1
End If
Loop
'
'Having reached this point, one of the lists has finished.
'Display the remaining unique values.
If lngAIdx < lngMaxAIdx Then
For lngAIdx = lngAIdx to lngMaxAIdx
WScript.Echo "Remaining Unique A value: " & ListA(lngAIdx)
Next
End If
'
If lngBIdx < lngMaxBIdx Then
For lngBIdx = lngBIdx To lngMaxBIdx
WScript.Echo "Remaining Unique B value: " & ListB(lngBIdx)
Next
End If
WScript.Quit