Finding a match in strings in 2 columns n 2 sheets and if match found copy cell to sheet 1

clairexcel · May 6th 2022

Hello everyone,

I have been trying to come up with a solution on my own for this, but unfortunately I am stuck.

I have a file with two sheets, with item codes and relative info and prices.

On sheet one I have the main code of the items in column A. On sheet 2 in column A I have the secondary code (the component of the main item code)

Item codes (the main and the secondary) are similar in the middle, they differ in the first character and in the last/last two. They will have the 13 characters (after the initial one, identical) E.G:

Main Item code is X1234567891123GT
Secondary code for the item above will be: R1234567891123YQ

I have a file with many thousands rows So I need a macro, to look in Sheet 2 Column A for a match (in the 13 characters after the first character) to item codes in Colum A SHeet 1and if match found it should copy the row starting cell D sheet 1 (so adjacent to the main item code matching).

Any input, suggestion or help is very appreciated.

thank you in advance.

**KjBox** · May 6th 2022

Quote

and if match found it should copy the row starting cell D sheet 1 (so adjacent to the main item code matching).

Do you mean, when a match is found, you want to copy from Sheet1 column D onwards from to Sheet2 column D onwards in the matching Row?

Your sample file has no data in Column D onwards

clairexcel · May 6th 2022

Hi KjBox ,

I mean when a match in the string of 13 chars is found I would need the macro to copy the cell A from Sheet 2 into Sheet1 in the corresponding row of the corresponding Main Code. I attach again an example of how I need it to be done. I highlighted two matching codes, and copied from sheet 2 the corresponding row, pasted it in Sheet 1 starting in cell D (which is empty)

Thank you for your help!COMPARE TWO COLUMNS FOR STRING MATCH BUT ONLY 13 CHARACTERS AFTER THE FIRST ONE-2.xlsx

**alansidman** · May 6th 2022

Try this:

Code

Option Explicit


Sub Foo()
Dim i As Long, j As Long
Dim lr As Long, lr2 As Long
Application.ScreenUpdating = False
lr = Foglio1.Range("A" & Rows.Count).End(xlUp).Row
lr2 = Foglio2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lr
For j = 2 To lr2
If Mid(Foglio1.Range("A" & i), 2, 13) = Mid(Foglio2.Range("A" & j), 2, 13) Then
Foglio1.Range("D" & i) = Foglio2.Range("A" & j)
End If
Next j
Next i
Application.ScreenUpdating = True
MsgBox "complete"
End Sub

Display More

**KjBox** · May 7th 2022

This will be faster, especially for thousands of rows of data

Code

Option Explicit

Sub Compare()
    Dim x, y, z, i&, ii&
    
    x = Foglio1.Cells(1).CurrentRegion.Columns(1)
    y = Foglio2.Cells(1).CurrentRegion.Columns(1)
    ReDim z(1 To UBound(x, 1) - 1, 1 To 1)
    For i = 2 To UBound(x, 1)
        x(i, 1) = Mid(x(i, 1), 2, 13)
    Next
    For i = 2 To UBound(y, 1)
        If Not IsError(Application.Match(Mid(y(i, 1), 2, 13), x, 0)) Then
            ii = Application.Match(Mid(y(i, 1), 2, 13), x, 0)
            z(ii - 1, 1) = y(i, 1)
        End If
    Next
    Foglio1.Cells(2, 4).Resize(UBound(z, 1)) = z

End Sub

Display More

clairexcel · May 7th 2022

KjBox and alansidman thank you so very much.

~~I wanted to give you likes but I can't find the Like icon anymore, whay is that?~~ Edit: found the like icons :))

Tried them both, the faster is KjBox's code, I mean it literally finds any match in no time... and there are many many rows.

I will study them both anyway, and learn.

I really appreciate your help a BIG thank you!!!

clairexcel · May 7th 2022

KjBox , If you wish of course, could you explain to me (just me learning and trying to understand) two things please :

1. If the 13 chars match I want the code to copy also columns B and C from sheet 2. I am currently trying to figure the code in order to tell it " if match found copy also B and C columns from sheet 2 to sheet one, starting colum D.

I guess I have to give another variable, besides z, and then add a Foglio1.Cells(2, 5).Resize(UBound(v, 2)) = v kind of thing? Or change the Redim of z?

2. Match(Mid(y(i, 1), 2, 13), x, 0) - how does this function reasonate?

I mean, I get the 13 part :)) because it refers to the 13 chars the cells have in common. But why the 2? and the X,0? How does the code understand that it has to ignore the first letter of the string?

Thank you in advance for any explanation.

It's just that your codes are very complex, fast and I want to not only apply them but also to learn.

thanks!

**KjBox** · May 7th 2022

Use this modified code to copy columns A, B & C from Foglio2 to Foglio1 when a match is found. I have added comments to help you understand the code.
Mid(y(i, 1), 2, 13) The Mid function has 3 elements: String of Characters, Starting point, Number of Characters. So, y(i, 1) is the string, 2 is the starting point (first character is ignored), 13 is the number of characters to return from the starting point onwards.
Application.Match(Mid(y(i, 1), 2, 13), x, 0) VBA does not have a Match function so the "Application." bit tells the code to use the worksheet function MATCH, which has 3 elements: String to match, Array to search for a match, Exact or close match. So, Mid(y(i, 1), 2, 13) is the string that is to be matched, x is the array to search and 0 is "search for exact match"

Code

Option Explicit

Sub MatchCopy()
    Dim x, y, z, i&, ii&

' Load arrays x & y (x with just column A of Foglio1, y with all data from Foglio2)
    x = Foglio1.Cells(1).CurrentRegion.Columns(1)
    y = Foglio2.Cells(1).CurrentRegion
    
' Redim z to hold all matching data (the -1 is to ignore the column header)
    ReDim z(1 To UBound(x, 1) - 1, 1 To 3)
    
' Loop through array x to remove first character and keep next 13 characters from each entry
    For i = 2 To UBound(x, 1)
        x(i, 1) = Mid(x(i, 1), 2, 13)
    Next
    
' Loop through array y and check for match of the same 13 characters in Column A with array x
    For i = 2 To UBound(y, 1)
        If Not IsError(Application.Match(Mid(y(i, 1), 2, 13), x, 0)) Then
            
        ' If match found then load array z, in the appropriate row, with data from columns A, B & C of array y
            ii = Application.Match(Mid(y(i, 1), 2, 13), x, 0)
            z(ii - 1, 1) = y(i, 1): z(ii - 1, 2) = y(i, 2): z(ii - 1, 3) = y(i, 3)
        End If
    Next
    
' Place the contents of array z onto Foglio1 in columns D, E & F
    Foglio1.Cells(2, 4).Resize(UBound(z, 1), 3) = z

' Adjust the width of Foglio1 column D & E to suit added data
    Foglio1.Columns(4).Resize(, 2).AutoFit
    
End Sub

Display More

The code is faster because it is an "array based" code as opposed to an "object based" code.

An array based code first loads all required data into arrays, those arrays can then be manipulated and/or modified within the machine memory before place modifications back to a worksheet.

An object based code has to constantly refer back to a worksheet to place data and update, then pick up the next bit of data to check.

The array based code refers to a worksheet just once to get data and once to place data, hence much faster.

clairexcel · May 7th 2022

KjBox you are just so awsome !!!

I knew it it was this I had to write (from the code you have given me last year with the machines sheets):

z(ii - 1, 1) = y(i, 1): z(ii - 1, 2) = y(i, 2): z(ii - 1, 3) = y(i, 3)

But I wrote it wrong, to say the least :)))

The only thing I got right is ReDim z(1 To UBound(x, 1) - 1, 1 To 3) this line I modified correctly.

Thank you so very much for the code and also for the explanation, I really will study the lines and your explanations.

Thank you from the heart!

**KjBox** · May 7th 2022

You're welcome

Finding a match in strings in 2 columns n 2 sheets and if match found copy cell to sheet 1

clairexcel May 7th 2022

Participate now!

Share

Similar Threads

Save Created sheet with different name

Dynamic Formula To Lookup Most Recent Data On Or Before Given Data Based On Multiple Criteria

Matching a name across 2 sheets and then pulling a value from sheet2 and marking sheet1 with a yes based on partial text

Autofilter and return cell values using VBA