Search for Keywords and Return the Appropriate Item

Hi everyone. I am having a really tough time figuring out whether this could be done or not, and I am just lost. Basically, we often get mobile phone orders in Excel format but the items listed are in a different format from the ones we have in our inventory system.

Please refer to the attachment.

In the Item List tab, you will find under the Item List column, a list of phones as they are named in our inventory system.

However, in the Order list tab, you will see a list of odered phones that are either named in a different format or are a different variant of the same phone on the Inventory Item list.

So what I did was create keywords that are associated with a specific phone. So for example, for HTC Touch Dual - P5310 (cell A7 in the Item List tab) , it's associated keywords are:

P5310 , Touch Dual , and Neon 400 . HTC is the Manufacturer.

Now in cell A4 of the Order List tab, it says HTC P5310 SIL . This should therefore be assigned HTC Touch Dual - P5310 as per the inventory item list.

So basically, I was hoping for some formula or macro that would look at the ordered item, then check if it contains the manufacturer name, then check if it contains any one of the keywords, then return the associated inventory item to the cell beside the order item.

So for HTC P5310 SIL in cell A4 in the Order List tab, it sees that it's manufacturer is HTC ... so now check if it contains any of the keywords. Since it contains P5310, then it has identified that it is an HTC Touch Dual - P5310 as per the inventory item list. And vice versa. The result should be something like in the Result tab.

I hope I'm not being confusing. Anyways, any assistance would be much appreciated. This is a huge problem for me and I'm getting really really desperate.

Try the attached

Originally Posted by millz

Try the attached

Holy smokes, looks like it works. I will try it with a larger dataset tomorrow. Awesome awesome! Thank you. Just one question ... is there a way to make it so that doesn't matter whether lower or upper case?

This should ignore casing now

Originally Posted by millz

This should ignore casing now

Thank you millz. It works great. I've hit another snag though. I feel terrible for bringing it up.

Is there a way so that the macro recognizes the keyword if it is within a text string.

So for example, say the Inventory Item is "HTC X710A Raider LTE" with a keyword "X710A" .

But say, the Order Item is "HTC Raider(X710A)" .

Is there a way for the macro to still recognize the keyword "X710A" even though the order item has "(X710A)" instead ?

This should do what you want and faster when you have bulky data.

Originally Posted by jindon

This should do what you want and faster when you have bulky data.

Thank you Jindon. It works great. I've hit another snag though. I feel terrible for bringing it up.

Is there a way so that the macro recognizes the keyword if it is within a text string.

So for example, say the Inventory Item is "HTC X710A Raider LTE" with a keyword "X710A" .

But say, the Order Item is "HTC Raider(X710A)" .

Is there a way for the macro to still recognize the keyword "X710A" even though the order item has "(X710A)" instead ?

Originally Posted by jindon

This should do what you want and faster when you have bulky data.

Hi Jindon. Sorry I have another question if you don't mind. Your code works perfect when the Order List items are in column A. Which part of the code should I modify so that it works if the Order List items are in column L? I just can't seem to figure it out.

Option Explicit
Option Compare Text

Sub test()
    Dim a, b, i As Long, ii As Long, iii As Long, myItem As String
    With Sheets("order list").Cells(1).CurrentRegion
        .Columns(2).Offset(1).ClearContents
        a = .Value
        b = Sheets("item list").Cells(1).CurrentRegion.Value
        For i = 2 To UBound(a, 1)
            If a(i, 1) <> "" Then
                myItem = Split(a(i, 1))(0)
                For ii = 2 To UBound(b, 1)
                    If b(ii, 2) = myItem Then
                        For iii = 3 To UBound(b, 2)
                            If b(ii, iii) = "" Then Exit For
                            If Replace(a(i, 1), " ", "") Like _
                                myItem & Replace(b(ii, iii), " ", "") & "*" Then
                                a(i, 2) = b(ii, 1): Exit For
                            End If
                        Next
                        If a(i, 2) <> "" Then Exit For
                    End If
                Next
            End If
        Next
        .Value = a
    End With
End Sub

So what would be the logic to determine the keyword?

Or I should ask why it is not "HTC"?

The logic should be that first it should find the manufacturer (HTC). Then it looks for the 1st keyword (X710A). If the manufacturer and 1st keyword are there, then it has determined that the inventory item name should in fact be HTC X710A Raider LTE, as per the inventory item list.

If the 1st keyword is not found, then it looks for the 2nd keyword (say, Raider). If the manufacturer and 2nd keyword is there then it has determined that the inventory item name is, again, HTC X710A Raider LTE . And so on and so forth.

The problem is that an ordered item may contain a keyword but have something else attached, like a parenthesis. So an ordered item might look like HTC Raider (X710A) instead of just HTC Raider X710A. The keyword X710A is there, but it isn't recognized because there are parenthesis attached.

Then better to post a workbook with all the possible combination example data and the result that you want.

Originally Posted by jindon

Then better to post a workbook with all the possible combination example data and the result that you want.

Hi Jindon. I have attached a workbook. It also contains the macro that you posted. The desired result is in the Result tab. When I run the macro, it works really well, but hits a snag when it looks at cell A3 in the Order List tab.

Try change

                        For iii = 3 To UBound(b, 2)
                            If b(ii, iii) = "" Then Exit For
                            If Replace(a(i, 1), " ", "") Like _
                                myItem & Replace(b(ii, iii), " ", "") & "*" Then
                                a(i, 2) = b(ii, 1): Exit For
                            End If
                        Next

to

                        For iii = 3 To UBound(b, 2)
                            If b(ii, iii) = "" Then Exit For
                            If Replace(Replace(Replace(a(i, 1), " ", ""), "(", ""), ")", "") Like _
                                myItem & Replace(b(ii, iii), " ", "") & "*" Then
                                a(i, 2) = b(ii, 1): Exit For
                            End If
                        Next

Try replace all of a(i,2) with a(i,12)

Otherwise I need to see exact sheet layouts.

Originally Posted by jindon

Try replace all of a(i,2) with a(i,12)

Otherwise I need to see exact sheet layouts.

Hi Jindon. Thank you for your speedy reply. I did as you suggested but I get a runtime error 13 type mismatch and this piece of code gets highlighted:

 
For i = 2 To UBound(a, 1)

I have attached the actual workbook I am using.

Is this how you wanted?

Option Explicit
Option Compare Text

Sub test()
    Dim a, b, i As Long, ii As Long, iii As Long, myItem As String
    With Sheets("Order List")
        With .Range("l3", .Range("l" & Rows.Count).End(xlUp)).Resize(, 2)
            .Columns(2).Offset(1).ClearContents
            a = .Value
            b = Sheets("Item List").Cells(1).CurrentRegion.Value
            For i = 2 To UBound(a, 1)
                If a(i, 1) <> "" Then
                    myItem = Split(a(i, 1))(0)
                    For ii = 2 To UBound(b, 1)
                        If b(ii, 2) = myItem Then
                            For iii = 3 To UBound(b, 2)
                                If b(ii, iii) = "" Then Exit For
                                If Replace(a(i, 1), " ", "") Like _
                                    myItem & Replace(b(ii, iii), " ", "") & "*" Then
                                    a(i, 2) = b(ii, 1): Exit For
                                End If
                            Next
                            If a(i, 2) <> "" Then Exit For
                        End If
                    Next
                End If
            Next
            .Value = a
        End With
    End With
End Sub

Works great. Thank you so much Jindon, you are a genius! I can only hope one day to be as good as you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I am too slow again.. but will post my code to see if I was on the right track..

I took a different approach.. and made it show multiple matches separated by a "/".

For example:
Samsung R351
showed (in column M)
Samsung R351 / SAMSUNG LINK SOLO R351 / SAMSUNG LINK R351

Private Sub CommandButton2_Click()
    Dim arr, SS1
    Dim S1 As String, S2 As String, SArray As String, Response1 As String
    Dim LastRowAdd As String, FirstRowAdd As String
    Dim LastRow As Long, FirstRow As Long, LastRowSearch As Long
    Dim i As Long, j As Long, c As Long
    Application.ScreenUpdating = False
    For j = 4 To Range("L" & Rows.Count).End(xlUp).Row
        SS1 = Split(Cells(j, 12).Value, " ")
        S1 = SS1(0)
        S2 = SS1(1)
        Sheets("Item List").Range("A1:M" & Sheets("Item List").Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 2, "*" & S1 & "*"
        FirstRowAdd = FirstVisibleValue(ActiveSheet, 1)
        FirstRow = Range(FirstRowAdd).Row
        LastRow = Sheets("Item List").Range("A" & Rows.Count).End(xlUp).Row
        LastRowAdd = Sheets("Item List").Cells(LastRow, "A").Address
        If Sheets("Item List").Range(FirstRowAdd).Value = "" Then GoTo NotFound
        For i = FirstRow To LastRow
            c = 0
            SArray = Sheets("Item List").Cells(i, 1).Value
            arr = Split(SArray, ",")
            If S1 = "" Then Exit Sub
            Response1 = IsInArray(S2, arr)
            If Response1 = "True" Then
                c = 1
                cntS2 = cntS2 + c
            End If
            If c = 1 And cntS2 = 1 Then
                Cells(j, 13).Value = Sheets("Item List").Cells(i, 1).Value
            ElseIf c = 1 And cntS2 > 1 Then
                Cells(j, 13).Value = Cells(j, 13).Value & " / " & Sheets("Item List").Cells(i, 1).Value
            End If
NotFound:
        Next i
        cntS2 = 0
    Next j
    Application.ScreenUpdating = True
End Sub

In a Module:

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function


Function FirstVisibleValue(ByRef Sht As Worksheet, ByVal FilterCol As Long)
    Dim R As Range
    If Sheets("Item List").AutoFilterMode Then
        Set R = Sheets("Item List").AutoFilter.Range
        FirstVisibleValue = R.Offset(1, FilterCol - 1).Resize(R.Rows.Count, 1).SpecialCells(12).Cells(1).Address
    End If
End Function