Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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
    Attached Images Attached Images
    If you are a fool at forty, you will always be a fool

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Differences (Office 97 SR2)

    Braddy

    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. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Braddy
    If you are a fool at forty, you will always be a fool

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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
    If you are a fool at forty, you will always be a fool

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Find Differences (Office 97 SR2)

    Hi

    Thanks for the formula it was just what I wanted.

    Thanks again

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Braddy
    If you are a fool at forty, you will always be a fool

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Differences (Office 97 SR2)

    Braddy

    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. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •