Compare lines in two sheets

[k0st4din]

Hello,
Is there a way in VBA Excel one full line to compare with each other? In sheet1 A1:H1 to A2500:H2500 has information, on sheet2 A1:H1 to A10000:H10000 has other information. I need to do the following thing, line A1:H1 on Sheet1 to compare it to each row of sheet2, if in sheet2 row is found, whose cells contain the same information as in A1:H1 on Sheet1 to earn value I1, if there is no row with the same information, I1 is empty, the same should be repeated with the other 2499 rows.
Thanks in advance for the help rendered by you.

[p45cal]

Sub blah()
With Sheets("Sheet1")
  For Each rw1 In Intersect(.UsedRange.EntireRow, .Range("A:H")).Rows
    For Each rw2 In Intersect(Sheets("Sheet2").UsedRange.EntireRow, Sheets("Sheet2").Range("A:H")).Rows
      For i = 1 To 8
        If Not rw1.Cells(i) = rw2.Cells(i) Then Exit For
      Next i
      If i = 9 Then .Cells(rw1.Row, "I").Value = "Line " & rw2.Row
    Next rw2
  Next rw1
End With
End Sub

[snb]

or

Sub M_snb()
    sn = Filter([transpose(if(sheet2!A1:A100="","~",sheet2!A1:A100&sheet2!B1:B100&sheet2!C1:C100&sheet2!D1:D100&sheet2!E1:E100&sheet2!F1:F100&sheet2!G1:G100&sheet2!H1:H100))], "~", False)
    
    sp = Sheet1.Cells(1).CurrentRegion
    For j = 1 To UBound(sp)
       If UBound(Filter(sn, Join(Application.Index(sp, j, 0), ""))) > -1 Then MsgBox "I found  " & Join(Application.Index(sp, j, 0), "")
    Next
End Sub

[k0st4din]

Many thanks p45cal, it's possible.
In your example snb, just let me climb the inscription, but in column "I:I", I do not show it which row is the record of "Sheet2".
Thank you very much.

This is further off-topic question: Why can not you give reputations?

[p45cal]

Just had a thought, are there multiple rows to find on sheet2? If not, I should have aborted the search each time a row was found - at the moment it shows only the last row found, it would be faster. If there are multiple lines, do you want a list of all of them?

[snb]

You could have adapted the code yourself:

Sub M_snb() 
    on error resume next
    sn = Filter([transpose(If(sheet2!A1:A100="","~",sheet2!A1:A100&sheet2!B1:B100&sheet2!C1:C100&sheet2!D1:D100&sheet2!E1:E100&sheet2!F1:F100&sheet2!G1:G100&sheet2!H1:H100))], "~", False) 
     
    sp = Sheet1.Cells(1).CurrentRegion 
    For j = 1 To UBound(sp) 
        sheet1.cells(j, ubound(sp,2)+2)=Application.match(Join(Application.Index(sp, j, 0), ""),sn,0) 
    Next 
End Sub

[mancubus]

This is further off-topic question: Why can not you give reputations?

just click the asterisk * below user name in any post.

[k0st4din]

Hello p45cal, with the first macro that make it work the way I want (I'll attach a picture to see), the second macro that made snb and he now works.
I do not understand your question - >> "If there are multiple lines, do you want a list of all of them?" - What do you mean? If you do it the macro to try what would be the difference and I'll try it.
mancubus here is made ​​by me picture by pressing the star that does not allow me -> Link to download

[p45cal]

Hello p45cal"If there are multiple lines, do you want a list of all of them?" - What do you mean?

For each line on sheet1, you want to know where it is to be found on sheet2. What if, for a given line on sheet1, there are more than one similar lines on sheet2?

[k0st4din]

You have no idea how right you are. I never thought of that.
I'd appreciate if you did for me. Thus if sheet2 has more rows with the same information, I'll bear it in sheet1. That's what you have in mind?
Thank you very much.

[ElderEphany]

This is very educational content and written well for a change. It's nice to see that some people still understand how to write a quality post.

[p45cal]

try:

Sub blah2()
With Sheets("Sheet1")
  For Each rw1 In Intersect(.UsedRange.EntireRow, .Range("A:H")).Rows
    With .Cells(rw1.Row, "I")
      .ClearContents
      For Each rw2 In Intersect(Sheets("Sheet2").UsedRange.EntireRow, Sheets("Sheet2").Range("A:H")).Rows
        For i = 1 To 8
          If Not rw1.Cells(i) = rw2.Cells(i) Then Exit For
        Next i
        If i = 9 Then
          If Len(.Value) = 0 Then
            .Value = "Line " & rw2.Row
          Else
            .Value = Replace(.Value, "Line ", "Lines ") & ", " & rw2.Row
          End If
        End If
      Next rw2
    End With
  Next rw1
End With
End Sub

If it's slow I can speed it up.

[k0st4din]

This is enough, I just have no words with which to say thank you.
I will say this I thank you for being there to help us unknowing how to do it.
Bow before you.

[p45cal]

Were there any duplicate lines on sheet2?

[k0st4din]

Yes, it was in the original file after I placed the macro. There were two identical lines.

[snb]

so this could work too:

Sub M_snb() 
    On Error Resume Next
 
    sn = Filter([transpose(If(sheet2!A1:A100="","~",sheet2!A1:A100&sheet2!B1:B100&sheet2!C1:C100&sheet2!D1:D100&sheet2!E1:E100&sheet2!F1:F100&sheet2!G1:G100&sheet2!H1:H100))], "~", False) 
     
    sp = Sheet1.Cells(1).CurrentRegion 
    For j = 1 To UBound(sp) 
        sheet1.cells(j, UBound(sp,2)+2)=Application.match(Join(Application.Index(sp, j, 0), ""),sn,0)
        sn(Application.match(Join(Application.Index(sp, j, 0), ""),sn,0)-1)=""
    Next 
End Sub

[p45cal]

so this could work too:

It doesn't seem to.
Don't forget that sn is zero based so:
sn(Application.match(Join(Application.Index(sp, j, 0), ""),sn,0))=""
deletes the wrong entry and I think should be:
sn(Application.match(Join(Application.Index(sp, j, 0), ""),sn,0)-1)=""

but having deleted the entry it doesn't then look for another.
Finally, hopefully there should be no blanks in column A of sheet2 amongst the data.

[snb]

@p45cal


You are right about:
sn(Application.match(Join(Application.Index(sp, j, 0), ""),sn,0)-1)=""

Thank you, I amended the code.

I don't think you are right here:
but having deleted the entry it doesn't then look for another.
every time a match is found the match will be deleted, so the next time the next match will be found

Nor here:
Finally, hopefully there should be no blanks in column A of sheet2 amongst the data.
They have been singled out by the first line in the code.

[p45cal]

Well, I tried this on sample data. Regarding "it doesn't then look for another", you say

so the next time the next match will be found

but there won't be a next time; the snippet:

For j = 1 To UBound(sp) 
        sheet1.cells(j, UBound(sp,2)+2)=Application.match(Join(Application.Index(sp, j, 0), ""),sn,0) 
        sn(Application.match(Join(Application.Index(sp, j, 0), ""),sn,0)-1)="" 
    Next

executes the code inside the loop once, and once only, per value of j (the rows on sheet1), so Match on the first line is only done once (per value of j), it also needs to be done again until no more matches are found, so a While..Wend or Do Loop Until might be needed within the For j= loop.

Regarding "no blanks in column A of sheet2 amongst the data", yes, it does filter those out, even if they are amongst the data to be searched, so if there are some, the indices of sn will no longer tally with the row numbers in sheet2 below any blanks in column A.

[snb]

Amended into:

Sub M_snb()
    sn = Filter([transpose(If(sheet2!A1:A100="","~",sheet2!A1:A100&sheet2!B1:B100&sheet2!C1:C100&sheet2!D1:D100&sheet2!E1:E100&sheet2!F1:F100&sheet2!G1:G100&sheet2!H1:H100))], "")
    sp = Sheet1.Cells(1).CurrentRegion

    For j = 1 To UBound(sp)
        c00 = Join(Application.Index(sp, j, 0), "")

        Do Until UBound(Filter(sn, c00)) = -1
            Sheet1.Cells(j, UBound(sp, 2) + 2) = Sheet1.Cells(j, UBound(sp, 2) + 2) & ";" & Application.Match(c00, sn, 0)
            sn(Application.Match(c00, sn, 0) - 1) = ""
        Loop
    Next
End Sub