Compare Datasets or Arrays

[Justin Emlay]

I have two lists. These can be in either table form or array. That is, my
data is in a dataset which I can move to an array if need be. ListA is a
master list and it contains all items. ListB contains the same items but
not necessarily all the items. I need to know which items are missing.

I remember messing with a compare array function but now I can't seem to
find it anywhere. Ideas? I would rather NOT loop through each item in
ListA and check to see if it exists in ListB as the lists are HUGE!

I already have

For each item in ListA
For each item in ListB
If not A=B then record data
Next
Next

This takes about 45 minutes to run.


The compare function I remember using did the following:

ArrayA
1
2
3
4
5

ArrayB
2
3
4

Thus ArrayC
1
5


This would still work however a Dataset level function would work better and
moving the Datasets into arrays would take some time as well.


Thanks for any help or insight!

 

[Cor Ligthert]

Justin,

This is the most classic dataprocessing what already whas done on Hollerith
machines. It was called "matching" in that cardpunch concept. That is still
the fastest.

It is doing going foreward in the arrays by matching everytime the keys of
that, it assumes that both arrays are sorted on the key value.

When that is not the case than you can make first from arrayB a tempory
hashtable/sortedlist and than match the keys of arrayA if they are in that
and set than the values to true.

The hashtable/sortedlist gives than at the end the missing members.

I hope this helps?

Cor

 

[Guest]

two suggestions
1/ compare the number of elements in each array first - you may not have to
do any comparing!

<<IF>> list1 is static and list2 is dynamic
2/ flag each element in list 1 when it is added to list 2, and deflag when
it is removed
that way you only have to check the flags in list1

hth

guy

 

[Jay B. Harlow [MVP - Outlook]]

Justin,
If ListA & ListB are in a dataset I would leave them as DataTable. I would
make sure that each (ListB specifically) has a Primary Key.

You can then use something like:

Dim listA As New DataTable("listA")
listA.PrimaryKey = New DataColumn() {listA.Columns("key")}

Dim listB As New DataTable("listB")
listB.PrimaryKey = New DataColumn() {listB.Columns("key")}

For Each row As DataRow In listA.Rows
If Not listB.Rows.Contains(row!key) Then
' found missing row
End If
Next

The Contains method is overloaded for multiple value keys...

Alternatively you could define a DataView over listB with the fields you are
matching on.

Dim listA As New DataTable("listA")
Dim listB As New DataTable("listB")

Dim viewB As New DataView(listB)
viewB.Sort = "field1, field2, field3"

Dim keys(2) As Object
For Each row As DataRow In listA.Rows
keys(0) = row!field1
keys(1) = row!field2
keys(2) = row!field3
If viewB.Find(keys) = -1 Then
' found missing row
End If
Next

The Find method is overloaded for single value keys...

Hope this helps
Jay

 

[Justin Emlay]

Thanks, that helped a lot!

Justin,
If ListA & ListB are in a dataset I would leave them as DataTable. I would
make sure that each (ListB specifically) has a Primary Key.

You can then use something like:

Dim listA As New DataTable("listA")
listA.PrimaryKey = New DataColumn() {listA.Columns("key")}

Dim listB As New DataTable("listB")
listB.PrimaryKey = New DataColumn() {listB.Columns("key")}

For Each row As DataRow In listA.Rows
If Not listB.Rows.Contains(row!key) Then
' found missing row
End If
Next

The Contains method is overloaded for multiple value keys...

Alternatively you could define a DataView over listB with the fields you
are matching on.

Dim listA As New DataTable("listA")
Dim listB As New DataTable("listB")

Dim viewB As New DataView(listB)
viewB.Sort = "field1, field2, field3"

Dim keys(2) As Object
For Each row As DataRow In listA.Rows
keys(0) = row!field1
keys(1) = row!field2
keys(2) = row!field3
If viewB.Find(keys) = -1 Then
' found missing row
End If
Next

The Find method is overloaded for single value keys...

Hope this helps
Jay