Partial Matching between two sheets

[zios007]

Hi Everyone! I'm new here and new to VBA. I'm trying to automate few things but I've spent lots of hours already looking on the internet, videos, forums, etc... and haven't been able to achieve anything, no success.

Here is what I'm trying to achieve, let see if I know how to explain it clearly.

- I have sheet1 with a list of descriptions
- I have sheet2 with a list of references
- The references in sheet2 can be found in the sheet1 list of descriptions but it is not an exact match since the descriptions have a lot of gibberish text in front and in the back of the reference.
- I need to do a partial match check from the reference in sheet2 with sheet1
- If the reference from sheet2 exists in any of the sheet1 descriptions (if it matches), then I would like to return columns b, c and d from sheet2 to columns in sheet1 f, h and i.
- Note: There may be times in sheet2 that one of the columns may not have data since it will get provided later. So if no data I would like to leave a blank field in sheet1.

I've been trying to test only to return c from sheet2 to h in sheet1 to start with, but not success.

These are my 2 sheets examples:

Sheet1:

Sheet2:

And here is a piece of code that I've been trying, but not results appear anywhere. If the code is crap, please change it as you prefer. I'm already too lost... maybe it is easier coding it differently.

Sub Find_ISL_REF()

 Dim rng2 As Range, c2 As Range, cfind As Range
    Dim x, y
    With Worksheets("Sheet1")
        Set rng2 = .Range(.Range("C2"), .Range("C2").End(xlDown))
        For Each c2 In rng2
            x = c2.Value
            With Worksheets("Sheet2").Columns("A:A")
                On Error Resume Next
                Set cfind = .Cells.Find(what:=x, lookat:=xlPart, LookIn:=xlValues)
                If (Not (cfind Is Nothing)) Then
                    y = cfind.Offset(0, 5).Value
                    c2.Offset(0, 5) = y
                End If
            End With
        Next c2
    End With

End Sub

I hope one of you brilliant and intelligent members can help me out. I'm sure it is much easier that I've been trying to code it for. But at this point, after hours of researching and testing, I come here for help.

Thank you very much in advance. I will appreciate any help.

Bests,
ZioS

 

[Alex Blakenburg]

I put all the accounts in the same column. However, because there are duplicated account numbers, I need to figure out 1st the DeptID to description will be the applicable one for the account. So I've added a column D on the Accounts sheet. Any suggestion on how to do this?

I am a bit unclear on what you want here.
If you are not cleaning up the duplicates, then do you want to get
• The first occurence of the Account No (using column B to do the lookup)
If this is the case we should just switch to using VLookup)
OR
• Do we need to match on BOTH Account No and Dept ID ?

Also in the Ref table only the utilities account 5868 has both Acct no AND description. Can we change that to just being account no 5868 like the other accounts. We can then to a much cleaner lookup to the CoAccts table.