CODEWITHSUNDEEP
vbaexcel
Pia
Pia

Reputation: 1

Excel - Find matches between two arrays (one with value and one with value embedded in text string)

I found a similar question in a different post called "In Excel I need to find data matches between two arrays - one horizontal and one vertical" and I found part of my answer there. Unfortunately, I did not see how I could add a sub-question to this post. So here is the case.

I have an Excel workbook with two sheets:

1) In Sheet "Vendors" in column A, I listed all my vendor names (around 40).

2) In Sheet "Data" I have different data filled from column B until column Z. In this "Data" sheet in column F and/or column L the vendor name is listed for each row BUT the vendor name is embedded in more text. This text is never the same. It could be the vendor name with a space followed by numbers or the vendor name followed by a dash without space in between and then followed by numbers (e.g. "Vendor name-233" or "Vendor name / gfjd").

What I need the VBA to do is to use the vendor names in cloumn A of the "Vendor" sheet and to match it with the vendor names that are contained in the text strings of columns F and/or L of the "Data" sheet. The VBA should then return the found vendor name for each row in column A of the "Data" sheet.

This is the VBA that I tried from the previous post. The big problem is that it would only return the vendor name if the exact value is found in the "Data" sheet. The VBA cannot work with the vendor name embedded in the text string.

Sub newtest() Dim data, reference As Range Dim skipsome As Boolean skipsome = False

Set reference = Worksheets("Vendors").Range("A1", "A40")

Set data = Worksheets("Data").Range("B2", "F6")
For Each dataCell In data
    For Each referenceCell In reference
        If dataCell.Value = referenceCell.Value Then
                Worksheets("Data").Cells(dataCell.Row, 1).Value = dataCell.Value
                skipsome = True
                Exit For
        End If

        If skipsome = True Then
            skipsome = False
            Exit For
        End If
    Next
Next

End Sub

I would really appreciate if you could help me.

Thanks, Pia

Upvotes: 0

Views: 992

Answers (1)

David G
David G

Reputation: 2347

You need to use Instr to see if the vendor name is CONTAINED in the value. Replace

If dataCell.Value = referenceCell.Value Then

with

If Instr(dataCell.Value, referenceCell.Value) <> 0 Then

This only works as is if there are no vendor names than englobe others.

Upvotes: 0

Related Questions