Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
minimize the file size in a ppt with collections of many pivot tables | Excel Discussion (Misc queries) | |||
LOOKUPS - Creating LOOKUPs where two different values must BOTH be satisfied. | Excel Worksheet Functions | |||
Comparing Collections | Excel Programming | |||
Intersecting/Unioning Collections | Excel Programming | |||
Creating Collections 'on the fly' | Excel Programming |