PDA

View Full Version : [SOLVED] Compare lines in two sheets



k0st4din
10-24-2013, 07:39 AM
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
10-24-2013, 08:19 AM
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
10-24-2013, 08:48 AM
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
10-24-2013, 10:34 AM
Many thanks p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal), it's possible.
In your example snb (http://www.vbaexpress.com/forum/member.php?44644-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
10-24-2013, 10:57 AM
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
10-24-2013, 02:06 PM
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
10-24-2013, 03:42 PM
This is further off-topic question: Why can not you give reputations?

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

k0st4din
10-24-2013, 10:03 PM
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 (http://www.sendspace.com/file/zy6gyp)

p45cal
10-24-2013, 10:40 PM
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
10-25-2013, 01:08 AM
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. :bow:

ElderEphany
10-25-2013, 02:24 AM
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
10-25-2013, 03:46 AM
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
10-25-2013, 03:56 AM
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
10-25-2013, 04:09 AM
Were there any duplicate lines on sheet2?

k0st4din
10-25-2013, 04:25 AM
Yes, it was in the original file after I placed the macro. There were two identical lines. :hug:

snb
10-25-2013, 04:33 AM
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
10-25-2013, 05:55 AM
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
10-25-2013, 06:52 AM
@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
10-25-2013, 07:15 AM
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 foundbut 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
10-25-2013, 07:55 AM
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