Complex if Function

[cmohnkern]

Hi Chandoo,

I am sifting through invoices and I need to find invoices adjustments that have different check numbers than the original invoice. For example, Column A lists all of the invoices and column B lists the check number; so, I would want the function to highlight invoices 11111, 11111ADJ, 44444, 44444ADJ because these original invoices and adjusting invoices do now have the same check number.

Is this possible?

Invoice Number Check Number
11111 23456
11111ADJ 23455
22222 67890
222222ADJ 67890
33333 88888
33333ADJ 88888
44444 19876
44444ADJ 19875
55555 12345
55555ADJ 12345

 

[prasaddn]

Hi,
With assumption that you have all the invoice numbers sorted:
Try this formula in one helper column and filter for all False.
=IF(RIGHT(A2,3)="ADJ",AND(LEFT(A2,LEN(A2)-3)=TEXT(A1,"General"),B2=B1),"")

Regards,
Prasad DN

 

[Somendra Misra]

Hi,

See the file. The Invoice with no discrepancy in Check Number will be highlighted using CF and than you can filter on color "No Fill" to get the required.

Regards,