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

Solved/Closed
Report
Posts
4
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
August 1, 2013
-
 Scr33nager -
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

7 replies

Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
Posts
4
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
August 1, 2013

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
Posts
4
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
August 1, 2013

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

Cheers
Phil
0
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
Posts
4
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
August 1, 2013

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
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
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
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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