Any part of string in cell matches part of string in another [Solved/Closed]

Scr33nager 4 Posts Tuesday July 30, 2013Registration date August 1, 2013 Last seen - Jul 30, 2013 at 12:06 PM - Latest reply:  Scr33nager
- Aug 6, 2013 at 12:22 PM
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
See more 

9 replies

TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Aug 1, 2013 at 10:36 AM
0
Thank you
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 4 Posts Tuesday July 30, 2013Registration date August 1, 2013 Last seen - Aug 1, 2013 at 10:50 AM
0
Thank you
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 4 Posts Tuesday July 30, 2013Registration date August 1, 2013 Last seen - Aug 1, 2013 at 11:00 AM
0
Thank you
Sorry, I must have started writing my post before you replied.. hopefully, my revision makes this more feasible.

Cheers
Phil
TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Aug 1, 2013 at 11:28 AM
Hi Phil,

A lot has changed.

Let's say you have 10 rows.
Compare A1 to the 9th,10th,11th and 12th digits of B1:B10.
When match is found, place the found value in it's entirety (all 13 digits) in C1.
Then do the same for A2 and so on.

Does this sound like what you meant?

Do you have a fixed amount of rows (then how many) or is it dynamic?

Best regards,
Trowa
Scr33nager 4 Posts Tuesday July 30, 2013Registration date August 1, 2013 Last seen - Aug 1, 2013 at 11:39 AM
Hi Trowa,

First of all, thank you for looking at this.. I really appreciate your help.

Yes, your summary is correct. There are 1239 rows of data for column A and 4476 rows for column B. If you would like me to upload an example file I can do that ... just let me know where.

Thanks again

Regards
Phil
TrowaD 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Aug 5, 2013 at 10:35 AM
0
Thank you
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
0
Thank you
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 2396 Posts Sunday September 12, 2010Registration dateContributorStatus July 19, 2018 Last seen - Aug 6, 2013 at 11:50 AM
0
Thank you
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
0
Thank you
waheyyy! It's working now :-)

Thank you so much for your help Trowa.. really appreciate it..

Kind regards
Phil