Return Value By Multiple Matching And Unmatching Criteria

Mr Big Dave · Jan 31st 2008

I am developing Spreadsheet that you will enter a list of parts that you need. it will have many columns but only 4 are vital to the check. this will be generated for each project.

With each part i want to then look up in another file that is a list of all the possible parts you can have with there cost. this file will be standard for everything and it will be updated from time to time.

now not all the criteria to look up will be exact matches.

here is an example

code temp/pres rating diameter material
GLV 4500 15 16Mo3

in the other file we will have multipu entries for GLV with different values.

code temp/pres rating diameter material Cost Reference
GLV 4500 20 16Mo3 400 aaa1
GLV 5500 15 16Mo3 350 aab1
GLV 5000 15 16Mo3 300 aab2
GLV 5000 15 13CrMo4-5 600 acb1
GLV 5500 15 13CrMo4-5 600 acb2

For Code i need it to match (there are lots of codes even including variations like GLV.)
For Temp/pres rating i need it to be equal or higher to the value
For diameter i need a match
for material i need a match.

so as you can see from the list above there are 2 options. so then i want it to return the cheapest.

that is step one.

step two is to repeat it for multiple list of parts.

Thanks for you help. and if you have any questions let me know.

i have searched the forum for a couple hours and did not find what i needed. but if you know one that is best fitted for me then let me know.

thanks again

Brian Walters · Jan 31st 2008

Re: Look Up And Return Value By Multiple Matching And Unmatching Criteria

Welcome to the forum.

The attached file should do what you need using the Database Functions. Enter your criteria in the green cells.

HTH

Bill Rockenbach · Jan 31st 2008

Re: Look Up And Return Value By Multiple Matching And Unmatching Criteria

Try this code. Assumes the part number you are looking for is in cell A1. Your workbook tables have to be open and the table in a sheet called "Table". Change names as required.

Code

Option Explicit
Dim FoundPartNbr As Range
Dim SplitFoundPartNbr
Dim Partnbr As String
Dim SplitPartNbr


Private Sub CommandButton1_Click()
    Dim FirstFind As String
    Dim Table As Range
    Dim MyTables
    Dim i As Integer


    MyTables = Array("Table1.xls", "Table2.xls", "Table3.xls")  'Names of your workbook tables


    For i = 0 To UBound(MyTables)
        MsgBox MyTables(i)
        Set Table = Workbooks(MyTables(i)).Sheets("Table").Range("Table1")
        Partnbr = Sheet3.Range("A1")
        SplitPartNbr = Split(Partnbr, " ")
        Set FoundPartNbr = Nothing


        Set FoundPartNbr = Table.Find(SplitPartNbr(0), LookIn:=xlValue, _
                                      lookat:=xlPart)
        CheckForMatch


        FirstFind = FoundPartNbr.Address


        Do Until FoundPartNbr Is Nothing
            Set FoundPartNbr = Table.FindNext(FoundPartNbr)
            If FoundPartNbr.Address = FirstFind Then Exit Do
            CheckForMatch
        Loop
    Next i
End Sub
Sub CheckForMatch()
    On Error GoTo NoMatches
    SplitFoundPartNbr = Split(FoundPartNbr, " ")
    If SplitFoundPartNbr(1) >= SplitPartNbr(1) And _
       SplitFoundPartNbr(2) = SplitPartNbr(2) And _
       SplitFoundPartNbr(3) = SplitPartNbr(3) Then
        MsgBox "Match = " & FoundPartNbr
    End If
    Exit Sub
NoMatches:
    MsgBox "No Matches Found"
    End
End Sub

Display More

Mr Big Dave · Jan 31st 2008

Re: Look Up And Return Value By Multiple Matching And Unmatching Criteria

Thank you all. will look at these in the morning when i am back in the office.

Bill Rockenbach · Jan 31st 2008

Re: Look Up And Return Value By Multiple Matching And Unmatching Criteria

Corrected and enhanced code from prior post. Assumes Tables are in a sheet named "Table" and the part table is a named range "Table" in each workbook and your part number to find is in cell A1.

Code

Option Explicit
Dim FoundPartNbr As Range
Dim SplitFoundPartNbr
Dim Partnbr As String
Dim SplitPartNbr
Dim Matches As Integer
Dim MyTables
Dim i As Integer




Private Sub CommandButton1_Click()
    Dim FirstFind As String
    Dim Table As Range


    MyTables = Array("Table1.xls", "Table2.xls", "Table3.xls") 'your table workbook names


        Partnbr = Range("A1")
        SplitPartNbr = Split(Partnbr, " ")
        Matches = 0


    For i = 0 To UBound(MyTables)


        Set Table = Workbooks(MyTables(i)).Sheets("Table").Range("Table")


        Set FoundPartNbr = Nothing
        Set FoundPartNbr = Table.Find(SplitPartNbr(0), LookIn:=xlValue, _
                                      lookat:=xlPart)
        If FoundPartNbr Is Nothing Then GoTo NextTable


        CheckForMatch


        FirstFind = FoundPartNbr.Address


        Do Until FoundPartNbr Is Nothing
            Set FoundPartNbr = Table.FindNext(FoundPartNbr)
            If FoundPartNbr.Address = FirstFind Then Exit Do
            CheckForMatch
        Loop


NextTable:
    Next i
    If Matches = 0 Then MsgBox "No Matches Found "
End Sub




Sub CheckForMatch()
    SplitFoundPartNbr = Split(FoundPartNbr, " ")
    If SplitFoundPartNbr(1) >= SplitPartNbr(1) And _
       SplitFoundPartNbr(2) = SplitPartNbr(2) And _
       SplitFoundPartNbr(3) = SplitPartNbr(3) Then
        MsgBox "Match = " & FoundPartNbr.Value & " in table """ & MyTables(i) & """"
        Matches = Matches + 1
    End If


End Sub

Display More

[hr]*[/hr] Auto Merged Post;[dl]*[/dl]My firt posted code was for part numbers as a single string. This code is for part numbers in idividual columns. Assume looked for part number is in cells A1:A4

Code

Option Explicit
Dim FoundPartNbr As Range
Dim SplitFoundPartNbr(3)
Dim Partnbr As String
Dim SplitPartNbr(3)
Dim Matches As Integer
Dim MyTables
Dim i As Integer
Dim j As Integer




Private Sub CommandButton2_Click()
    Dim FirstFind As String
    Dim Table As Range


    MyTables = Array("Table1.xls", "Table2.xls", "Table3.xls") 'your table workbook names
    Matches = 0


        For j = 0 To 3
        SplitPartNbr(j) = Cells(1, j + 1)     
        Next j


    For i = 0 To UBound(MyTables)


        Set Table = Workbooks(MyTables(i)).Sheets("Table").Range("Table")


        Set FoundPartNbr = Nothing


        Set FoundPartNbr = Table.Find(SplitPartNbr(0), LookIn:=xlValue, _
                                      lookat:=xlPart)
        If FoundPartNbr Is Nothing Then GoTo NextTable


        CheckForMatch


        FirstFind = FoundPartNbr.Address


        Do Until FoundPartNbr Is Nothing
            Set FoundPartNbr = Table.FindNext(FoundPartNbr)
            If FoundPartNbr.Address = FirstFind Then Exit Do
            CheckForMatch
        Loop


NextTable:
    Next i
    If Matches = 0 Then MsgBox "No Matches Found "
End Sub




Sub CheckForMatch()
For j = 0 To 3
    SplitFoundPartNbr(j) = FoundPartNbr.Offset(0, j)
Next j


    If SplitFoundPartNbr(1) >= SplitPartNbr(1) And _
       SplitFoundPartNbr(2) = SplitPartNbr(2) And _
       SplitFoundPartNbr(3) = SplitPartNbr(3) Then
        MsgBox "Match = " & FoundPartNbr.Offset(0, 0) & " " & _
        FoundPartNbr.Offset(0, 1) & " " & _
        FoundPartNbr.Offset(0, 2) & " " & _
         FoundPartNbr.Offset(0, 3) & " " & _
         FoundPartNbr.Offset(0, 4) & " " & _
        FoundPartNbr.Offset(0, 5) & " " & _
        " in table """ & MyTables(i) & """"
        Matches = Matches + 1
    End If


End Sub

Display More

Return Value By Multiple Matching And Unmatching Criteria

Participate now!

Share