Displaying the difference.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

the-jackal - 08 Oct 2007 17:17 GMT

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.

Signature

the-jackal

Reply to this Message

Chip Pearson - 08 Oct 2007 22:53 GMT

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.

Signature

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

> 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.
[quoted text clipped - 4 lines]
>
> Any ideas guys.

Reply to this Message