Error in finding the exact match

Question

I have three Sheets, Sh1 , Sh2, Sh3. Sh3 is my result sheet.

I have copied Column N from Sh1 to Sh3 in column E.

I consider column E of sheet3, then I compare the Column A of sheet2, If they match, then I will copy the result in sheet3 in column F.

I am able to find the matching columns, but, I have few Special cases, which I am not able to sort out.

I have explained them in the Image .

[![The Image Shows an example of how the ID Looks in column E of sht3][1]][1]

![Here i have shown both structure of ID. The first row id is the same, and has no Problem ist finding the match. In the second row, there is an 0 less in my sheet2, and the code Fails to Display that, in row 3, the Id has an 0 extra in sheet2. in row 4, i have an id with other id, but in sheet2, i have the same id, with 0 extra, same in row 5 as well, The ID are generally 11 to 13 Digit Long. ]1

Could someone suggest how I could overcome this issue. Below is the code, I am using to copy the values from one sheet to another and looking for the values in another sheet.

Sub lookup()
Dim lLastrow As Long
Dim rng As Range
Dim i As Long

ThisWorkbook.Sheets("S").Select
lLastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Range("P5:P" & lLastrow).Copy Destination:=Sheets("Result_APQP").Range("E5")
Range("G5:G" & lLastrow).Copy Destination:=Sheets("Result_APQP").Range("H5")

Sheets("Result").Select
For i = 5 To lLastrow

Set rng = Sheets("P").UsedRange.Find(Cells(i, 5).Value & "*", LookAt:=xlWhole)
'If it is found put its value on the destination sheet
If Not rng Is Nothing Then
Cells(i, 6).Value = rng.Value
 End If
Next i
End Sub

Could you explain a little bit more definitely what IDs need to be equal? As far as I understood we could ignore 0 while comparing IDs, am I right? — Vlad
Hi Mikz, can you explain a bit more what you are expecting to happen when: 1) There is an extra zero in the ID of Sheet 2 and 2) There is a zero less in the ID of Sheet 2. Or is it that the copying functions is removing the zero's? — maxhob17
@Vlad the Id in the first column is the ID in sheet1, and ID in second column is ID in sheet2, I am looking for ID in sht1 to match with ID in sht2. in cases, where i have mentioned, they fail and i am not able to match the ID. — Mikz
@maxhob17 No, the copying function is not recognising the Zeros. Eventhough the Zeros are less or more in my Sheets, considering the first 11 Digits it should match with each other, and row 4 and 5 are spl case, in this case, also, i want the ID to be matched and displayed in sheet3. — Mikz

Vlad Vlad · Accepted Answer · 2017-07-12T10:29:30

I would do something like that:

Sub clear(rng As Range)
    For Each cell In rng.Cells
        cell.Value = Split(cell.Value, ",")(0)
        cell.Value = Split(cell.Value, ";")(0)
        While Mid(cell.Value, Len(cell.Value)) = "0"
            cell.Value = Mid(cell.Value, 1, Len(cell.Value) - 1)
        Wend
    Next cell
End Sub


Sub lookup()
    Dim lLastrow As Long
    Dim rng As Range
    Dim i As Long

    ThisWorkbook.Sheets("S").Select
    lLastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    Range("P5:P" & lLastrow).Copy Destination:=Sheets("Result_APQP").Range("E5")
    Range("G5:G" & lLastrow).Copy Destination:=Sheets("Result_APQP").Range("H5")

    Call clear(Sheets("Result").UsedRange)
    Call clear(Sheets("P").UsedRange)

    Sheets("Result").Select
    For i = 5 To lLastrow

        Set rng = Sheets("P").UsedRange.Find(Cells(i, 5).Value & "*", LookAt:=xlWhole)
        'If it is found put its value on the destination sheet
        If Not rng Is Nothing Then
            Cells(i, 6).Value = rng.Value
        End If
    Next i
End Sub

Note than the clear() subprogramm modifies the original data.

Error in finding the exact match

1 Answers