How can I avoid doubles?

[s7y]

Hello everybody. I have attached a test workbook where you can easily see what the problem is: in ULD I have some data in columns A, B, C and D. In Loadsheet I recall the data by the use of Vlookup. The problem is that I get doubles (when I input the same weight I get twice the same ULD). Is it possible to get some sort of userform that has the data of all the columns for those columns that have the same weight for me to choose (in the test case an userform with 2300 - 12345 - q7 gyd and 2300 - 55555 - ord). Of course if one of the double has already been chosen the choice will not be necessary anymore and the values that have not yet been used should be automatically be used.



I hope my request make some kind of sense

Thank you all for any help

[macropod]

For matching with repeated data, see:
http://windowssecrets.com/forums/sho...l=1#post734296
http://www.techsupportforum.com/foru...ml#post2567119

[Peter Wienand]

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!

 
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.

[s7y]

hello Peter, I have copied and pasted the function in VBA under Loadsheet. I then copied the formula for both cells A and B but in both cases I get #NAME as result. What do I do wrong?

[Peter Wienand]

You will need to insert the custom function into your workbook as follows

1. Open your workbook
2. Press ALT-F11 to open the VB Editor
3. Right-click on the VBAProject for your workbook in the Project Explorer (left hand panel by default) and click Insert -> Module
4. Paste the custom function code into the main panel

Note that the function will only be available within the workbook to which you copy the code to.

[s7y]

Peter, I have done as you told me and it works. I have a last question that concerns this matter: what if I have to select multiple ranges in Countif? in my real sheets I have ranges A13:AZ13 and J17:AX17 and G26:S26 and AE26:AN26. I know that countif cannot handle multiple ranges and I have tried with +countif but it doesn't work

[Peter Wienand]

I would have thought that adding multiple COUNTIF functions together should work. Would you be able to post a copy of your formula?

You could also try the COUNTIFS function, which is probably a more elegant solution than using multiple COUNTIF functions.

http://office.microsoft.com/en-us/ex...010047494.aspx

[s7y]

hi there Peter, here you have the formula as I use it

=vlookupx(A13;ULD!$A:$D;2;0;COUNTIFS($A13:$BB13;A1 3);(J17:AY17;A13);(G26:U26;A13);(AE26:AP26;A13))

[Peter Wienand]

Try this

=vlookupx(A1,$A:$D,2,0,COUNTIFS($A13:$BB13,A13,J17 :AY17,A13,G26:U26,A13,AE26:AP26,A13))

[s7y]

hello Peter, I have tried the formula as you passed it but I still get the #VALUE error. Why is that that in the formula you refer to A1? and... why don't we refer to the ULD! sheet anymore? I ask you these questions because I would like to learn from this.

[Peter Wienand]

My apologies, I made some changes to the cell references when I was trying to get the formula working and I forgot to change them back.

=vlookupx(A13,ULD!$A:$D,2,0,COUNTIFS($A13:$BB13,A1 3,J17:AY17,A13,G26:U26,A13,AE26:AP26,A13))

Note that your original formula was logically correct - it just had a few syntax errors. In particular the parameters of the two functions must be separated by commas ',' and contained within a set of ()'s so the formula needs to take this form...

=vlookupx( <cell containing lookup value> , <range of cells to lookup in> , <column number to look up> , <0 or 1> , COUNTIFS( <range of cells 1> , <criteria for range of cells 1> , <range of cells 2> , <criteria for range of cells 2> , ... ))

[s7y]

Hello Peter, what if the separator I use is ;?

[Peter Wienand]

You will need to replace all of the ,'s in the formula that I posted earlier with ;'s and it should work.

[s7y]

hello Peter, sorry for the late reply but I have been away... all works fine, thank you very very much for your help