Highlight rows while comparing two lists

[wollyka]

Hi
I have 2 sheets OD and Rep in the Excel.
The first one contains a list of overdue and the second sheet has the daily payments.

I want to compare the two lists and highlight from sheet OD the applications that got payments (from sheet REP).For example There are four applications number 3004279903 in OD but the borrower paid only once (as you see in sheet REP), so i should highlight row number 9 only and leave rows 10, 11, 12 intact in sheet OD. How can i do that?

Thanks

 

[bosco_yip]

Try,

Select B9:H60 >> Conditional Formatting >> select "New Rule" >> select "Use a Formula….."

>> in the rule box, enter :
=(COUNTIFS($D$9:$D9,$D9)*COUNTIFS(REP!$C$2:$C$34,$D9)+(COUNTIFS($D$9:$D$60,$D9)>1))=2

>> click Format >> choose cell background color >> OK

Regards
Bosco

 

[wollyka]

Try,

Select B9:H60 >> Conditional Formatting >> select "New Rule" >> select "Use a Formula….."

>> in the rule box, enter :
=(COUNTIFS($D$9:$D9,$D9)*COUNTIFS(REP!$C$2:$C$34,$D9)+(COUNTIFS($D$9:$D$60,$D9)>1))=2

>> click Format >> choose cell background color >> OK

Regards
Bosco

Hi
Thanks for your help. It does the job quite good. However, now i have another situation. Your formula is highlighting the first row with the application number 3004279903 in Sheet OD, however if there are 2 applications (payments) with the number 3004279903 in Sheet REP, the first two rows in Sheet OD should be highlighted. The process should be like this:
1- Compare the applications number in Sheet REP with Sheet OD.
2- If there is a match, compare the credit amount in REP with the Installment amount in OD
3- If the credit amount is less than the installment amount, do nothing. If it is equal, then highlight the first row with the same application number in Sheet OD.
Thanks again for your time and help

 

[bosco_yip]

Maybe,

Changed the Conditional Formatting formula into :

=COUNTIFS(REP!$C$2:$C$35,$D9,REP!$D$2:$D$35,$G9)*(COUNTIFS($D$9:$D9,$D9)=1)

Regards

 

[wollyka]

Maybe,

Changed the Conditional Formatting formula into :

=COUNTIFS(REP!$C$2:$C$35,$D9,REP!$D$2:$D$35,$G9)*(COUNTIFS($D$9:$D9,$D9)=1)

Regards

Thank you very much, it works
However, if in Sheet REP, there are two or (X) payments with the number 3004279903, how can i modify the formula so that the first two (or X) rows in Sheet OD with the number 3004279903 and same installment amount are highlighted?
Regards

 

[bosco_yip]

Thank you very much, it works
However, if in Sheet REP, there are two or (X) payments with the number 3004279903, how can i modify the formula so that the first two (or X) rows in Sheet OD with the number 3004279903 and same installment amount are highlighted?
Regards

Then,

The CF formula again change into :

=IF($D9<>"",COUNTIFS($D$9:$D9,$D9)<=COUNTIFS(REP!$C$2:$C$35,$D9,REP!$D$2:$D$35,$G9))

Regards

 

[wollyka]

Then,

The CF formula again change into :

=IF($D9<>"",COUNTIFS($D$9:$D9,$D9)<=COUNTIFS(REP!$C$2:$C$35,$D9,REP!$D$2:$D$35,$G9))

Regards

Thank you very much...