How can I avoid doubles?

Peter Wienand · #3 05-07-2012, 05:16 AM

Here is a custom function that may be of assistance. Note that although I have done a little bit of testing, it has not been tested extensively, so please review the code and test that the function works as expected before depending on it!

Code:

 
Public Function vlookupx(lookup_value As Variant, table_array As Range, col_index_num As Integer, range_lookup As Boolean, occurrences As Integer) As Variant
 
    'Declare variables
    Dim i, count As Integer
    Dim ivalue As Variant
 
    On Error Resume Next
 
    'Protect against erroneous inputs
    If col_index_num > table_array.Columns.count Then
        vlookupx = CVErr(xlErrRef)
        Exit Function
    End If
    If occurrences < 1 Then
        vlookupx = CVErr(xlErrNA)
        Exit Function
    End If
    If range_lookup = True And lookup_value < table_array.Cells(1, 1).value Then
        vlookupx = CVErr(xlErrNA)
        Exit Function
    End If
 
    'If range_lookup = true then assume first column is sorted ascending
    'Finds largest value that is smaller than lookup_value if lookup_value not found
    If range_lookup = True Then
        count = 0
        For i = 1 To table_array.Rows.count
            ivalue = table_array.Cells(i, 1).value
            If ivalue > lookup_value Then
                vlookupx = table_array.Cells(i - 1, col_index_num).value
                Exit Function
            ElseIf ivalue = lookup_value Then
                count = count + 1
                If count = occurrences Then
                    vlookupx = table_array.Cells(i, col_index_num).value
                    Exit Function
                End If
            End If
        Next i
        vlookupx = table_array.Cells(table_array.Rows.count, col_index_num).value
        Exit Function
 
    'If range_lookup = false then look for exact matches
    Else
        count = 0
        For i = 1 To table_array.Rows.count
            ivalue = table_array.Cells(i, 1).value
            If ivalue = lookup_value Then
                count = count + 1
                If count = occurrences Then
                    vlookupx = table_array.Cells(i, col_index_num).value
                    Exit Function
                End If
            End If
        Next i
        vlookupx = CVErr(xlErrNA)
        Exit Function
    End If
    vlookupx = CVErr(xlErrNA)
 
End Function

The function should work the same way as the inbuilt vlookup function, just with an additional parameter (occurences). If occurences = 1, the function should return the same values as the inbuilt vlookup function. If occurences is 2, then the first match will be ignored and the second match returned and so on.

This function could be used in your example spreadsheet as follows

=vlookupx(B1,ULD!$A:$D,2,0,COUNTIF($A1:B1,B1))

Hope that helps.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge Doubles My Document	ExecAssist	Mail Merge	1	02-27-2012 04:06 PM
Table doubles up in MS Word 2010 but not in 2003	seguinju	Word	3	02-24-2012 11:07 PM
Avoid merging tables	b0x4it	Word	4	06-14-2011 10:01 PM
Is it possible for Excel print consistently with any printer? avoid awkward page brea	William C	Excel	1	12-21-2010 01:40 PM
How do I avoid Hourglass using API Function?	sauerj	Word	1	11-10-2010 06:53 AM