Related:

- Excel vba find partial match
- Excel vba partial string match - Best answers
- Vba find partial match - Best answers
- Excel partial match two columns - How-To - Excel
- Excel vba copy row if cell value matches ✓ - Forum - Office Software
- Excel vba search array for match - How-To - Excel
- Excel VBA - Find and Expand matching cell entries ✓ - Forum - Excel
- Excel: Partial Matches in 2 Different Cells ✓ - Forum - Excel

## 7 replies

TrowaD

- Posts
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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

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

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

Cheers

Phil

Cheers

Phil

TrowaD

- Posts
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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

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

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

Scr33nager

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

- Posts
- 2847
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- January 13, 2022

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 + 1If cell.Value - Mid(Range("B" & mRow), 9, 4) = 0Then 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,MyStringAs Integer For Each cell In Range("A1:A3") mRow = 0 Do mRow = mRow + 1MyString = Mid(Range("B" & mRow), 9, 4) If cell.Value = MyString ThenCells(cell.Row, "C").Value = Range("B" & mRow).Value End If Loop Until mRow = 10 Next cell End Sub

Best regards,

Trowa

Scr33nager

waheyyy! It's working now :-)

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

Kind regards

Phil

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

Kind regards

Phil