Comparing two workbooks for differences

Just incase it's actually two worksheets rather than two workbooks:

  • add a third sheet
  • in Sheet 3!A2 do =Sheet 1!A2=Sheet 2!A2
  • drag a cross and down until you reach the end of your data sets (i.e. If you had 26 columns and 400 rows of data, then drag to Z401).
  • conditional format Sheet 3 data to be red if it contains F.
  • filter for reds.

The built in tool is likely better, but this will spot the gaps for you.

Ideally you'd add a column as the second to last step and have a formula in there that identifies if one or more cells in that row is false, but I can't think of the best way to do this. You could so =IF(OR(A2="False",...) but there will be a much better way.

/r/excel Thread