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

Solved/Closed
Scr33nager Posts 4 Registration date Tuesday July 30, 2013 Status Member Last seen August 1, 2013 - Jul 30, 2013 at 12:06 PM
 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
Related:

7 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 1, 2013 at 10:36 AM
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
0
Scr33nager Posts 4 Registration date Tuesday July 30, 2013 Status Member Last seen August 1, 2013
Aug 1, 2013 at 10:50 AM
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
0
Scr33nager Posts 4 Registration date Tuesday July 30, 2013 Status Member Last seen August 1, 2013
Aug 1, 2013 at 11:00 AM
Sorry, I must have started writing my post before you replied.. hopefully, my revision makes this more feasible.

Cheers
Phil
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
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
0
Scr33nager Posts 4 Registration date Tuesday July 30, 2013 Status Member Last seen August 1, 2013
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 5, 2013 at 10:35 AM
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

Didn't find the answer you are looking for?

Ask a question
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Aug 6, 2013 at 11:50 AM
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
waheyyy! It's working now :-)

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

Kind regards
Phil
0