Displaying the difference.

  • Thread starter Thread starter the-jackal
  • Start date Start date
T

the-jackal

I have 2 reports of data both formatted the same and what I need to work
out are the differences between the two and just display those.

e.g Report 1 has for example A, B, D, F, G displayed and Report 2 has
A,B,C,D,E,F,G,H and what I want to see as an output is just C,E,H. can
this be done by filtering or are more advance formulae required?

Any ideas guys.
 
Assuming Report1 and Report2 are named ranges of single columns of data, you
can use the formula

=NOT(ISNA(MATCH(OFFSET(Report2,ROW()-ROW($A$1),0,1,1),Report1,0)))

to return TRUE or FALSE indicating if the value Report2 exists in Report1.
Fill this formula down for as many rows as you have data. Change the $A$1 to
the address (including the $ characters) to the address of the first cell
that contains the formula. Once you have these TRUE and FALSE values, you
can use Filter to display only the TRUE or only the FALSE value, which will
show what is in one range and not the other. Swap positions of 'Report1'
and 'Report2' in the formula to reverse the result.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Back
Top