how do I Compare two tables and find records without matches in excel

"meshell5" wrote:
how do I Compare two tables and find records without matches in excel

To keep things simple, copy over the other sheet into one of the 2 files
So now you have 2 sheets, eg: Sheet1, Sheet2 (assumed identical structure)

Checking Sheet2's col A against Sheet1's col A
In Sheet2,
Put in B2: =IF(A2="","",ISNUMBER(MATCH(A2,Sheet1!A:A,0)))
Copy down to the last row of data in col A. Col B flags the required records
w/o matches with FALSE. Apply autofilter on col B, filter in B1 for: FALSE.
Copy the filtered rows, paste special as values/formats in a new sheet

Repeat likewise in Sheet1 to check Sheet1's col A against Sheet2's col A
In Sheet1,
Put in B2, copy down: =IF(A2="","",ISNUMBER(MATCH(A2,Sheet2!A:A,0)))
Rest of steps similar.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

There are lots of ways of doing what you propose. I prefer this method,
which uses a simple macro:
Sub Compare2Shts()
For Each Cell In Worksheets("CompareSheet#1").UsedRange
If Cell.Value < Worksheets("CompareSheet#2").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next

For Each Cell In Worksheets("CompareSheet#2").UsedRange
If Cell.Value < Worksheets("CompareSheet#1").Range(Cell.Address) Then
Cell.Interior.ColorIndex = 3
End If
Next
End Sub

Hope that helps!!

Regards,
Ryan---


--
RyGuy


"Max" wrote:

"meshell5" wrote:
how do I Compare two tables and find records without matches in excel

To keep things simple, copy over the other sheet into one of the 2 files
So now you have 2 sheets, eg: Sheet1, Sheet2 (assumed identical structure)

Checking Sheet2's col A against Sheet1's col A
In Sheet2,
Put in B2: =IF(A2="","",ISNUMBER(MATCH(A2,Sheet1!A:A,0)))
Copy down to the last row of data in col A. Col B flags the required records
w/o matches with FALSE. Apply autofilter on col B, filter in B1 for: FALSE.
Copy the filtered rows, paste special as values/formats in a new sheet

Repeat likewise in Sheet1 to check Sheet1's col A against Sheet2's col A
In Sheet1,
Put in B2, copy down: =IF(A2="","",ISNUMBER(MATCH(A2,Sheet2!A:A,0)))
Rest of steps similar.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---