Any part of string in cell matches part of string in anotherSolved/Closed

Question

First post after hours of fruitless googling :-)

Working in Excel 2010..

I have merged two data sets and where there are instances of exact duplicate values in column 'A' and have separated these from my merged data set. 

Now, I want to see if any part of a cell value in column 'A' exists in another cell in column 'A', and, if column 'B' for both rows match, then return a value in column 'C' that will enable me to tie the two rows together

For instance, in 'A1' I have the value 'MN4741'. In 'A100' I have a value of '4247414' thus the matching string is '4741' and where 'B1' matches 'B100', I would like to populate 'C1' and 'C100' with 'MN4741'.

Is this possible? Or perhaps someone can suggest a better way to approach this!

Thanks for considering

TrowaD · Answer

Hi Scr33nager,

Well, Excel needs to know what to search for. I understand from your post that it is completely random, right?

If that is the case then I would say it is impossible.

I mean the value 'MN4741' would match any cell containing an M, N, 4, 7 or 1.

Is there a specific string you would like to search for? Like the last 4 numbers?

Best regards,
Trowa

Scr33nager · Answer

OK.. no replies so I have re-jigged the worksheet in an effort to make this less complicated. Please can someone help me with the correct formula for the following:-

Column A contains a list of numerics that are 4 digits in length. Column B contains a list of numerics that are 13 digits in length. I want to search for a match between the 4 digits in column A and the 9th,10th,11th and 12th digits in the column B array, and somehow link the rows together. 

So, if the 4 digits of 'A1', and each subsequent cell, match the string in the column B array starting at the 9th digit then let C1 = either the contents of column B or it's location.

Or is there a better way of doing this?

Thanks

Scr33nager · Answer

Sorry, I must have started writing my post before you replied.. hopefully, my revision makes this more feasible.

Cheers
Phil

TrowaD · Answer

Hi Phil,

Keep in mind that you are asking Excel to check 4476 rows 1239 times (more then 5,5 million comparisons!). Meaning it will take a while.

Here is the code:
Sub test()
Dim mRow As Integer

For Each cell In Range("A1:A1239")
    mRow = 0
    Do
        mRow = mRow + 1
        If cell.Value = Mid(Range("B" & mRow), 9, 4) Then
            Cells(cell.Row, "C").Value = Range("B" & mRow).Value
        End If
    Loop Until mRow = 4476
Next cell

End Sub
You can post your file using sites like www.speedyshare.com or ge.tt. This in case you might want to post a file in the future or the code isn't fulfilling your needs.

Best regards,
Trowa

Scr33nager · Answer

Hi Trowa,

I've been trying to get this working but it's really exposing my lack of excel/VBA knowlegde :-)

I have uploaded a cut-down version of my spread sheet... can you see any obvious errors?

[code]http://speedy.sh/axQvG/Match-Test.xlsm/code

Kind regards
Phil

TrowaD · Answer

Hi Phil,

It is because Excel needs to know we are working with numbers.
So we can do a calculation to let Excel know:
Sub StringComp()
Dim mRow As Integer

For Each cell In Range("A1:A3")
    mRow = 0
    Do
        mRow = mRow + 1
        If cell.Value - Mid(Range("B" & mRow), 9, 4) = 0 Then
            Cells(cell.Row, "C").Value = Range("B" & mRow).Value
        End If
    Loop Until mRow = 10
Next cell

End Sub
Or we can declare another variable:

Sub StringComp()
Dim mRow, MyString As Integer

For Each cell In Range("A1:A3")
    mRow = 0
    Do
        mRow = mRow + 1
        MyString = Mid(Range("B" & mRow), 9, 4)
        If cell.Value = MyString Then            Cells(cell.Row, "C").Value = Range("B" & mRow).Value
        End If
    Loop Until mRow = 10
Next cell

End Sub

Best regards,
Trowa

Scr33nager · Answer

waheyyy! It's working now :-)

Thank you so much for your help Trowa.. really appreciate it..

Kind regards
Phil

Any part of string in cell matches part of string in another

7 responses

Similar discussions

Subscribe To Our Newsletter!