Excel

Using a User Defined Function (UDF) to find and analyse duplicate items

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

chitosunday

Description:

Function lookm will find one-row or one-column range (known as a vector) for a value or duplicate value and return a value to the same position in a second one-row or multi-column range. 

Discussion:

Unlike the lookup function of excel which looks for the first item match or closest match, this function will look for the exact match; you can choose the no. of duplicates you want to find and also the "last item matched". 

Code:

instructions for use 

			
Option Explicit 
 
Function lookm(SearchItem, RangeFind As Range, RangeGetResult As Range, Optional NumDuplicate As Integer) 
     'SearchItem is the search item which can be text or range
     'RangeFind is the range of your data you want to find for your match
     'RangeGetResult is the range of your data you want to get
     'NumDuplicate is the number of duplicate instance it finds and default is last duplicate
    Dim ctr As Integer, i As Integer 
    For i = 1 To RangeFind.Count 
        If SearchItem = RangeFind.Cells(i) Then 
            ctr = ctr + 1 
            If NumDuplicate = 0 Then 
                lookm = RangeGetResult.Cells(i).Value 
            Else 
                If ctr = NumDuplicate Then 
                    lookm = RangeGetResult.Cells(i).Value 
                    Exit Function 
                End If 
            End If 
        End If 
    Next i 
    If ctr = 0 Then lookm = "" 
End Function

How to use:

  1. Create a module by pressing Alt + F11 simultaneously. If you do not see the directory, press ctrl r. Then right click the directory , add module.
  2. Open the module and put the code mentioned above. Close the module
  3. In your worksheet you can use this UDF function with the Syntax =lookm("item to be searched","the range where you want to find the data", the range where you want to get the data", the no. of duplicate instance)
  4. If you want to search for the last item matched, then specify 0 or don't put no. of duplicate instance because the default is last item
 

Test the code:

  1. Lookm can either be horizontal or vertical lookup as long as your "get range" is one row or column and your "find range" is also the same length . If if does not find anything, it will just produce a blank result.
  2. It is not necessary for the lookup range to be sorted.
 

Sample File:

lookupmulti.zip 11.38KB 

Approved by mdmackillop


This entry has been viewed 248 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express