+ Reply to Thread
Results 1 to 11 of 11

Comparing two data sets that don't line up on a line to line basis

  1. #1
    Registered User
    Join Date
    05-22-2012
    Location
    Calgary, canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Comparing two data sets that don't line up on a line to line basis

    Hi,

    I have two datasets, Invoices and Purchase Orders and I am trying to compare the subtotal amounts for them to make sure they are the same. Invoices has the names of all the employees who worked on it and all those employees are applied the same Purchase Order. So I use the subtotal function to sumup the PO.

    The other dataset has the lump sum amount of the POs.

    The problem is that the subtotals in the invoices dataset do not line up with the POs because the invoices has more rows (because it has the list of employees that worked on that).

    Is there any macro or conditional formating rule that can be applied to overcome this problem?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Comparing two data sets that don't line up on a line to line basis

    Try this in D2 and copy down:

    =IF(A2="",IF(C2-SUMIF($H$2:$H$108;B2;$I$2:$I$108)=0,"OK","Not OK"),"")

    Is it something like this you need?
    Sincerely
    Søren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

  3. #3
    Registered User
    Join Date
    05-22-2012
    Location
    Calgary, canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Comparing two data sets that don't line up on a line to line basis

    Thanks for the quick response.

    The formula you gave gives me an error.

    What I am trying to do is to compare the Subtotals in Column B and H and make sure that are same. However, the problem is that they do not line up properly on a line-to-line basis. For example, the PO Subtotal in Column B is on Row 5 where as the PO Subtotal in Column H is on the forth row.

    Any suggestions?

  4. #4
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Comparing two data sets that don't line up on a line to line basis

    EDIT: Try with this:


    =IF(A2="",IF(C2-SUMIF($H$2:$H$108,B2,$I$2:$I$108)=0,"OK","Not OK"),"")

    I have ";" as delimiters instead of ",". I forgot to change 2 of them.
    Last edited by Søren Larsen; 05-22-2012 at 03:01 PM. Reason: Elaboration

  5. #5
    Registered User
    Join Date
    05-22-2012
    Location
    Calgary, canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Comparing two data sets that don't line up on a line to line basis

    I replaced all the commas with semicolons but I am still getting the same error:
    'The Formula you typed contains an error'

  6. #6
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Comparing two data sets that don't line up on a line to line basis

    Yes, I edited post #4. Use the formula from there; it should work now. Sorry about the back and forth!

  7. #7
    Registered User
    Join Date
    05-22-2012
    Location
    Calgary, canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Comparing two data sets that don't line up on a line to line basis

    Hi Soren,

    Thank you so much for your help. There are however 5 subtotals that are being inaccurately displayed as 'Not Ok' even though the dollar amount match.

    They are Column B, Rows 80, 137, 157, 189, 192, 195.

    Any clues as to why these are being inaccurately labeled as 'Not Ok'?

  8. #8
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Comparing two data sets that don't line up on a line to line basis

    It's because they are not exactly equal; there is a difference of a very small number. Try the following instead for D2 (and copied down), it rounds the numbers to 2 digits before comparing them:

    =IF(A2="",IF(ROUND(C2,2)-ROUND(SUMIF($H$2:$H$108,B2,$I$2:$I$108),2)=0,"OK","Not OK"),"")

  9. #9
    Registered User
    Join Date
    05-22-2012
    Location
    Calgary, canada
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Comparing two data sets that don't line up on a line to line basis

    Thank you sooo much!!!

  10. #10
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Comparing two data sets that don't line up on a line to line basis

    You are very welcome!

  11. #11
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Comparing two data sets that don't line up on a line to line basis

    Remember to mark your thread as solved

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1