excelvba

Lookup unique "Values" if they follow a certain "Pattern" from a lookup table then return a "Name"

I have over 13000 unique barcode scan results stored in Sheet1 Column A. At some point similar results can occur that follows a certain pattern (out of 25 different one). The pattern can determinate a Part name. I would like to create a lookup table somewhere on Sheet2 where I could store all possible patterns and the corresponding part names. After I have set up the lookup table I would need a formula in Sheet1 Column B, dragged down that looks up each barcode scans from Column A and matches with the pattern and returns the part name from the look up table.

The following picture illustrates how I imagine the formula should work.

Illustration

For confirmation purposes on another process I already created some private functions in VBA, for example:

    Function PartName4Scan(s As String) As Boolean
        PartName4Scan= s Like "225299460502#[A-Z]##[A-Z]###"
    End Function

In the other process they work perfectly when they are used individually as a certain type is meant to be in a specific cell all the time.
=IF(PartName4Scan(E34)=TRUE,"O","X").
The problem is this time, I tried embedding them into a nested IF formula as the values in Column A are in random order like this: =IF(PartName4Scan(E34)=TRUE,"PartName4",IF(PartName5Scan(E34)=TRUE,"PartName5","X") and so on...

Of course after nesting them 3-4 times into each others I started experiencing performance issues so no way I could nest like 25 patterns together. It would be a lot easier if I can lookup the patterns and match them with the part names.

Solution

  • You are so close with the function idea. You can create a user-defined function that compares all the patterns and returns the actual part name.

    So in the workbook cell you can put =PartNameScan(A2) and it would return for example "Part 1"

    To implement this - create a code module in your workbook and create the function something like this:

    Function PartNameScan(s As String) As String
  
  If s Like "8298716602###" Then
    PartNameScan = "Part 1"
    Exit Function
  End If
    
  If s Like "225299460502#[A-Z]##[A-Z]###" Then
    PartNameScan = "Part 4"
    Exit Function
  End If
  
  'etc - add more patterns here
    
End Function