Find and Compare

[Nalex]

Hi,

I have two reports that I want to compare and then highlight the differences. my problem is that i have two billing reports 1) Claims Submitted and 2)Claims Paid.

I need the formula to match each record from the "Claims Paid" report to the "Claims Submitted" report based on Name, Code, and Date and then compare the Amount Paid.. If they dont match or the Claim is not found, highlight it Red...

I appreciate any help you can give me, and please let me know if you need any additional information.

Thanks

 

[Marcelo Branco]

Hi,

Do you want to highlight the rows in the Claims Submitted report or in the Claims Paid report?

Where they are? Same sheet? Ranges?

M.

 

[Nalex]

Hi,

I would like to highlight the "Claims Submitted" report... They are in the same workbook, but different sheets. the data range is A1:M150 on both sheets and a sample wold be as follows..

"Claims Submitted"
Name Date Code Amount
Joe S 5/2/11 111 40

"Claims Paid"
Name Date Code Amount
Joe S 5/2/11 111 30

In this example the 40 in the "Claims Submitted" sheet would be highlighted.

 

[Marcelo Branco]

Ok, but if you only 4 data-columns where they are in the range A:M?

Please tell me:

Name-column= ?
Date-column= ?
Code-column= ?
Amount-column= ?

M.

 

[Nalex]

M.

Sorry about that, I was trying to simplify my problem... There is more data in the row, but I just listed the items that needed to be matched and compared. To answer your question:

Name-column= A
Date-column= H
Code-column= C
Amount-column= I

Does this help? So sorry for the confusion.

 

[Marcelo Branco]

Hi Nalex,

First of all you have to create a named-ranged in Claims Paid sheet.

To do this select your data-range in this sheet A2:M? (assuming headers in row 1) and in the name-box (beside the formula-bar) type, say, MyData and hit Enter.

Go to Claims Submitted sheet

Select the Amount column, I2:I150 (assuming there is a header in I1) and:
Conditional Formatting>New Rule> Use a formula to...(last option)
and insert this formula

=IFERROR(IF(INDEX(INDEX(MyData,0,9),MATCH($A2&$C2&$H2,INDEX(MyData,0,1)&INDEX(MyData,0,3)&INDEX(MyData,0,8),0))=$I2,FALSE,TRUE),TRUE)

Format button and pick the format you want

HTH

M.
ps: If you are using Excel 2007 you may face a problem: Excel loses the CF after Save/Close and re-open (bug).
Tell me if this is happening.