Compare two tabs and only show exceptions

[Guest]

Hello,
Does anyone know how I can compare two seperate tabs of data and show
only the exceptions on a seperate tab? I am comparing data on two tables and
only need to know which ones don't match.

Thanks,

 

[Dave Peterson]

Cell by cell?

A1 with A1, Q12 with Q12, AC232 with AC232?

If yes, then you could use a program written by Myrna Larson and Bill Manville.

You can find a copy on Chip Pearson's site:
http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

 

[Guest]

Dave,
Sorry I should have been more specific. I am trying to match A1 with A1
and then if there is no match I want it to show all of the results in that
row (eg. A1 through A5). My end result would be any items that do not match
would be shown as exceptions and all the items that matched something do not
appear. Again I am trying to match specific cells but when there is an
exception I want to show the entire row.

Thanks,

 

[Dave Peterson]

If you're matching A1 with A1, then try Myrna and Bill's addin.

If you really mean you want to match up A1 with something that could be anywere
in column A, then you may want to try adding some helper columns and using
=vlookup() to return those values to the first sheet.

Then you could use some formulas to see if they matched up.

Key in column A.
Data in columns B:E
helper columns F:I
(=vlookup(a1,sheet2!a:e,2,0))
and change the 2 to 2, 3, 4 when you put the formula in F:I

Then
=if(b1=E1,"","Difference")
and drag to the right.

If you've never used =vlookup(), visit Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
for nice instructions.