Collections Lookups
 

Hello -

I am having a bit of trouble looking up and returning certain values
in multiple collections. Here is an example of my situation: I have
100 values in a single column range (Collection1) and 20 values in a
second single column range (Collection2). I would like to lookup the
values in Collection2 and determine if they exist in Collection1. If
they do not exist, I want to be able to capture them in a separate
worksheet. (An exception report if you will) It is fairly easy to
determine if they do exist but my main problem is handling the cases
where a match does not exist and an error results. Does anyone know
of an efficient way to handle the errors and capture the unmatched
data?

Collection1 is essentially a list of open cost centers and Collection2
is a list of cost centers that have been charged to in the past month.
I want to be able to determine if there are any cost centers being
charged to that are not currently open (i.e in collection1) and
display them in a separate sheet as an exception report.

Any help is greatly appreciated.

Dave

Collections Lookups
 

I assun=me that one uses the VLOOKUP function for this as
its probably the easiest. The issue is that is the
function fails to find a match, it generates an error. I
usually "wrap" this in a function that returns True or
False, or the index or a zero....
for example

Sub Test()
'' dim the collection etc first !

For Each Item in Collection2

If MissingItem(Item) then
ProcessNewItem Item
End If

Next

End Sub

Private Function MissingItem(Item) as Boolean
dim index as Long
' set a trap for the error
On Error Resume Next
Index = _
Application.WorksheetFunction.Match
(Item,ollection1,False)
' test error
If Err.Number = 0 Then
' OK
MissingItem = False
Else
Err.Clear
MissingItem = True
End If
' reset the error trap
On Error Goto 0
End Function


HTH
Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hello -

I am having a bit of trouble looking up and returning
certain values
in multiple collections. Here is an example of my
situation: I have
100 values in a single column range (Collection1) and 20
values in a
second single column range (Collection2). I would like
to lookup the
values in Collection2 and determine if they exist in
Collection1. If
they do not exist, I want to be able to capture them in
a separate
worksheet. (An exception report if you will) It is
fairly easy to
determine if they do exist but my main problem is
handling the cases
where a match does not exist and an error results. Does
anyone know
of an efficient way to handle the errors and capture the
unmatched
data?

Collection1 is essentially a list of open cost centers
and Collection2
is a list of cost centers that have been charged to in
the past month.
I want to be able to determine if there are any cost
centers being
charged to that are not currently open (i.e in
collection1) and
display them in a separate sheet as an exception report.

Any help is greatly appreciated.

Dave
.