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
Scr33nager - Aug 6, 2013 at 12:22 PM
Related:
- Vba find partial match
- Vba case like - Guide
- Music match jukebox - Download - Audio playback
- Number to words in excel formula without vba - Guide
- Vba check if value is in array - Guide
- Enable vba in excel - Guide
7 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 1, 2013 at 10:36 AM
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
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
Posts
4
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
August 1, 2013
Aug 1, 2013 at 10:50 AM
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
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
Posts
4
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
August 1, 2013
Aug 1, 2013 at 11:00 AM
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
Cheers
Phil
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 1, 2013 at 11:28 AM
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
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
Posts
4
Registration date
Tuesday July 30, 2013
Status
Member
Last seen
August 1, 2013
Aug 1, 2013 at 11:39 AM
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
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 5, 2013 at 10:35 AM
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:
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
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
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
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 6, 2013 at 11:50 AM
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:
Or we can declare another variable:
Best regards,
Trowa
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