Comparing 2 worksheets

Hi everyone,

Couple of months ago, I asked help here and was able to solve my problem. This is the link: http://www.excelforum.com/excel-form...96#post2847496

Sample of working file using Arlette's code: "Sept Sample 1.xlsx"

Anyway, everything was working fine until last week, I found a few duplicates in the data that was being compared and with thousands of rows, it's hard to do it manually. So my question is, is there a way to compare 2 columns instead? It would probably have to use Arlette's code since that one has the "first six, last 4" thingy (as shown on sample 1 above) and then additional code to counter-match it with another column.

Sample file with new columns: "Sept Sample 2.xlsx" with columns H and I from sheet 1 to be compared to E and F from sheet 2.

As always, any help is greatly appreciated

So which columns do you need to be matched?

Hi again Arlette

It's H and I from sheet 1 to be matched to E and F from sheet 2 on the Sample 2 file.

Give this a try

Option Explicit
Sub abc()
 Dim ptr As Long, i As Long, ii As Long, n As Long
 Dim a As Variant, b()

 With Worksheets("Sheet2")
    a = .Range("a3:f" & .Cells(Rows.CountLarge, "a").End(xlUp).Row)
        .Range("a1:f2").Copy Worksheets("Sheet1").Range("k1")
 End With

 With Worksheets("Sheet1")
    ReDim b(1 To .Cells(Rows.CountLarge, "a").End(xlUp).Row, 1 To 6)
    For ptr = 3 To .Cells(Rows.CountLarge, "a").End(xlUp).Row
        For i = 1 To UBound(a)
            If CStr(.Cells(ptr, "h")) = CStr(a(i, 5)) And Left(CStr(.Cells(ptr, "i")), 6) = Left(CStr(a(i, 6)), 6) And Right(CStr(.Cells(ptr, "i")), 4) = Right(CStr(a(i, 6)), 4) Then
                For ii = 1 To UBound(a, 2)
                    Select Case ii
                        Case Is = 5, 6
                            b(ptr - 2, ii) = "'" & a(i, ii)
                        Case Else
                            b(ptr - 2, ii) = a(i, ii)
                    End Select
                Next
             Exit For
            End If
        Next
    Next
    .Cells(3, "k").Resize(UBound(b), 6) = b
 End With
 
End Sub

Your welcome.