Comparing Data on two sheets

[Richard Smit]

Hi All,

I have yet another big project on hand. This is the requirement. I have two worksheets (Sheet1) and (Sheet2). I have some data on sheet1 and some on sheet 2. I will need to compare column A with Column A, if they match Shade them, and then compare Column B with Column B if they match shade them and then Column C with Column C. Now if all the three columns match, for which some will and some won?t. If all the three columns match, then I will to Copy the entire record to a new workbook/worksheet.

Looking forward to your help, thanks in advance.

Regards,
Richard.

[Bob Phillips]

What happens if say

Sheet1!A2=Smit
Sheet1!B2=Richard
Sheet1!C2=Planner

and

Sheet2!A2=Smith
Sheet2!B2=Joe
Sheet2!C2=Planner

should it be highlighting Sheet2!B2 when it is clear that it is not different to a corresponding row in Sheet1, the rows are different elements altogether.

[Richard Smit]

It should actually delete if all the three colms match.

Sheet1!A2=Smit
Sheet1!B2=Richard
Sheet1!C2=Planner

and

Sheet2!A2=Smith
Sheet2!B2=Joe
Sheet2!C2=Planner


Col A - Only contains Serial Numbers
Col B - Only contains Part Numbers
Col C - Only contains AlfNum

If Col A - Col A, Col B - Col B, Col C - Col C match, then the row can be deleted. Else the entire row needs to be transfered to another location or they can be as they are as long as the matching rows are deleted.

Note : It is the same. If Col A has a serial number then Column B has a partnumber associated to IT and also the AlfaNum.

I hope im not confusing you.

Please let me know if you need more information.

Regards,
Richard.

[Bob Phillips]

Yeah next question.

What if the 3 items in Sheet1 row don't match Sheet2 row 2, but they do with row 3? Is theier a key field to matc with?

[Richard Smit]

Cols 1 in sheet 1 should only match with content in Cols 1 in sheet 2 and likewise.

If Cols 1 in sheet 1 matches with Cols 1 in sheet 2 then check Cols 2 in sheet 1 with Cols 2 in sheet 2 then with Cols 3 in sheet 1 with Cols 3 in sheet 3. If all the three matches then delete the entire row. Even if One value does not match then do nothing.
Example.

Cols 1 in sheet 1 matches with Cols 1 in sheet 2 and
Cols 2 in sheet 1 matches with Cols 2 in Sheet 2 but
Cols 3 in sheet 1 does not match Cols 3 in sheet 2
Then keep the entire row

Cols 1 in sheet 1 matches with Cols 1 in sheet 2 But
Cols 2 in sheet 1 does not match Cols 2 in Sheet 2 and
Cols 3 in sheet 1 does not match Cols 3 in sheet 2
then keep the entire row.

This can be in combination, either rows.

[Bob Phillips]

Richard,

That is just re-stating what you have already said, it doesn't answer my last question.

Here is an example of what I mean

Sheet1

Serial Num Part Num Afl Num
X123 7-2 123
X123 9-2 345
X123 10-2 789

Sheet2

Serial Num Part Num Afl Num
X123 7-2 123
X123 10-2 789

Should row 3 on Sheet1 and row 2 on Sheet2 be deleted?

[Richard Smit]

My Apologies XLD, Yes, That is perfect.

- Richard

[Bob Phillips]

What is perfect? I asked a question, to which there are (at least) 2 responses.

[Richard Smit]

Sorry, i meant to say. yes, that is correct. It should be deleted.
If you can delete only the rows on Sheet 1, it would be great.

[Bob Phillips]

Well now I am really confused as to what you want. I don't know if it is you that has confused me, or whether I have confused myself <bg>.

Let's try some code



Public Sub ProcessData()
Dim i As Long
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
With sh1

LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With sh2

LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = LastRow1 To 1 Step -1

If 1 <= LastRow2 Then

If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value And _
sh1.Cells(i, "B").Value = sh2.Cells(i, "B").Value And _
sh1.Cells(i, "C").Value = sh2.Cells(i, "C").Value Then

sh1.Rows(i).Delete
End If
End If
Next i

End Sub

[Richard Smit]

Thanks XLD, however that did not work entirely. Only one row was deleted. not All matching rows. I noticed that is was looped, but not sure why it did not get through. please tell me if im missing something here.

Thanks and Regards,
Richard

[Bob Phillips]

Impossible to tell without more info, or a sample workbook.

[Richard Smit]

Alrighty! I've attached a sample workbook, all the rows shaded should be deleted in sheet one.

If you compare both the sheets you can see that I've shaded only the rows that match.

Hope this helps.

Regards,
Richard

[ironj32]

I actually am trying to accomplish the same thing (sort of). The problem with the code below is that it's comparing the two columns Row by Row (ie. Details!A5 to Owners!A5). Is there a way that it'll search the entire column to see if there's a match, and not do it row by row? The value "Frank" might exist in A5 of the Details sheet, but on the Owners sheet "Frank" exists in A20..therefore it doesn't find the match.


Public Sub FindUnmatchedData()
Dim i As Long
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = Worksheets("Detail")
Set sh2 = Worksheets("Owners")
With sh1

LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With sh2

LastRow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For i = LastRow1 To 5 Step -1

If 1 <= LastRow2 Then

If sh1.Cells(i, "A").Value = sh2.Cells(i, "A").Value Then
sh1.Cells(i, "A").Interior.ColorIndex = 2
Else
sh1.Cells(i, "A").Interior.ColorIndex = 6
End If

End If
Next i

End Sub

[ironj32]

I actually found the following Conditional Format to do what I need. But if you were to have a simple solution to the VBA approach, I would be interested to hear it. Below, "AppName" is the name of the list that I am comparing the cells to.

=COUNTIF(AppName,A50)=0

[IcePirate]

Hey,
Ok, I tried to attach a workbook but I couldn't get it under 2.MB even with just 10 lines so Im going to try and make it look like a work book here

So Imagine this is the workbook:
Sheet1 Column A:
123456
123457
123358

Sheet2 Column A:
123459
123457
123777
123456

Sheet3:
Column A | Column B


Now in sheet three column A, I want the numbers from sheet1 to display there. In column B, I want a Yes or a No
Yes = Yes the number in sheet1, matches a number in sheet2
No = If a No is displayed it means there is no number in sheet1 that matches sheet2

Now the reason for having all the numbers listed in sheet1 in sheet3 is because:
1) I need to know which numbers match and which ones dont
2) Sheet2 is 30,000 lines, Sheet1 is only 2,000 lines...
So there is going to be several numbers on sheet1 that wont be in sheet2 and vice versa.

So I need a formula or VB script that will list all the numbers on sheet1 on sheet3, then have the script display a yes or a no beside each number telling me - "Yes" the number is there, or "No" the number is not there

Hope that helps...

Thanks again!

[IcePirate]

Ok, here is a sample workbook.
View all three sheets inside the workbook.
This is a scaled down version -
My sheet1 is 2,000 lines, my sheet2 is 30,000 lines

I made it real simple...If you look at sheet3...When you see a yes - that means there is a number in sheet1 that matches a number somewhere in sheet2.
When you see a `no` on sheet3 - that means there is no number on sheet 1, that matches sheet 2

[IcePirate]

Forgot to post workbook