# Thread: Find Differences (Office 97 SR2)

1. ## Find Differences (Office 97 SR2)

Hi

Can anyone help please, I have two spreadsheets which I put together to try to find the differences, See screenshot.
I need to be a formula to tell when the either the gross or current price has changed for the same code

2. ## Re: Find Differences (Office 97 SR2)

You could do a pivot table of the results:
Row is col a data
Data is Std dev in B and/or StdDev of C
If the StdDev = 0 there has been no change, you could Autofilter to eliminate these in pivot table and you will be left with items that have changes in a/b

Steve

3. ## Re: Find Differences (Office 97 SR2)

If I understand you correctly you have two rows for each product - book1's item first and book2's item second. If so a simple set of formulae in columd D and E should do the trick. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

<table border=1><td></td><td>D</td><td>E</td><td>1</td><td></td><td></td><td>2</td><td>=B1=B2</td><td>=C1=C2</td></table>

Then select the rectangle of cells D1:E2 and drag down the sheet. Variants on the formulae could be used by wrapping the booleans in an IF

4. ## Re: Find Differences (Office 97 SR2)

Hi Andrew

At first your solution looked perfect until I realised the numbers were rounded for instance one was 8.8816 the other 8.88137796555086, rounded they look the same is there any way I can make them all the same. If it's not to much trouble.

Many thanks

5. ## Re: Find Differences (Office 97 SR2)

Hi

Thanks for your reply but I am a bit of a novice and don't understand pivot tables. So I went with Olivers reply.

Thanks anyway

6. ## Re: Find Differences (Office 97 SR2)

=round(b1,2)=round(b2,2)
=round(c1,2)=round(c2,2)

to check for 2 decimal places on each
Steve

7. ## Re: Find Differences (Office 97 SR2)

Hi

Thanks for the formula it was just what I wanted.

Thanks again

8. ## Re: Find Differences (Office 97 SR2)

Hi Andrew

I wonder if you have time to explain the last bit of your reply.
Variants on the formulae could be used by wrapping the booleans in an IF

9. ## Re: Find Differences (Office 97 SR2)

The result of the formula is a boolean (True or False)
sometimes it is neat to use an if statement to create more information - e.g. the amount of the difference or better formatting

The formula <font color=red> =round(b1,2)=round(b2,2) </font color=red> could be modified like this

<font color=red>=IF(round(b1,2)=round(b2,2),"","Differen t") </font color=red> to display nothing when they are the same, and the word "Different" when they are (visually easier to see)
or
<font color=red>=IF(round(b1,2)=round(b2,2),"",B2-B1) </font color=red> to display the difference whenever they were different
or
<font color=red>=IF(round(b1,2)=round(b2,2),"",(B2-B1)/B1*100%) </font color=red> to display the percentage change from B1
or
etc. etc. etc.

10. ## Re: Find Differences (Office 97 SR2)

Hi Andrew

Thank you very much for your explanation it's going to very useful as I have over 31,000 rows to evaluate.

I am continually overwhelmed by the help and friendship of the members of the lounge. It's sites like this that make the internet worthwile.

Grateful Thanks