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

1. ## 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?

2. ## 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?

3. ## 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. ## 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.

5. ## 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. ## 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. ## 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. ## 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. ## Re: Comparing two data sets that don't line up on a line to line basis

Thank you sooo much!!!

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

You are very welcome!

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

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

#### 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