Formula question.. hopefully not to difficult

[srosk]

I have a table with 47k records on it.

Column J has a number of values. I am concerned with the tracking user "jjj222"

Column D has Principal. Column E has repayment amount.

What I am trying to accomplish: If borrower (column K) has a balance (D-E) with user jjj222 (found in column J), show "Review" otherwise show No review necessary. I do not want "Review" to show when the line item has jjj222 in column J, but only when any other user is present in column J.

I hope I was able to explain this the best way. let me know if you have any questions! Thank you kindly!

 

[Joe4]

I am a bit confused. You seem to be contradicting yourself here:

Column J has a number of values. I am concerned with the tracking user "jjj222"

If borrower (column K) has a balance (D-E) with user jjj222 (found in column J), show "Review" otherwise show No review necessary.

I do not want "Review" to show when the line item has jjj222 in column J, but only when any other user is present in column J.

The third quote above seems to directly contradict your first two.

So which is it? If the first two statements are correct, then it would be something like:

=IF(AND(J2="jjj222",D2>E2),"Review","No Review Necessary")

If the last one is actually the correct one, then use:

=IF(AND(J2[COLOR=#ff0000]<>[/COLOR]"jjj222",D2>E2),"Review","No Review Necessary")

(notice the slight difference in red)

 

[srosk]

jjj222 is a lender. I want to see line items for anyone who has an open loan with jjj222, but I do not want to review line items where jjj222 is the lender.

So if borrower XYZ carries a balance with jjj222, and has an open loan with another lender, I would like it to say "Review" for all loans for borrower XYZ where the lender is anyone BUT jjj222.. if that makes anymore sense?

 

[Joe4]

Ah, that is where seeing some sample data might have helped explain that more clearly.

I think this works, but I fear it will be pretty slow on 47K records (this formula is for a record on row 2):

=IF(AND(SUMPRODUCT(--(K:K=K2),--(J:J="jjj222"),--(D:D>E:E))>0,SUMPRODUCT(--(K:K=K2),--(J:J<>"jjj222"),--(D:D>E:E))>0,D2>E2,J2<>"jjj222"),"Review","No Review Necessary")

You can speed it some by replacing all the whole column references with specific rows.
For example, if the data was in rows 2:47000, use this variation:

=IF(AND(SUMPRODUCT(--(K$2:K$47000=K2),--(J$2:J$47000="jjj222"),--(D$2:D$47000>E$2:E$47000))>0,SUMPRODUCT(--(K$2:K$47000=K2),--(J$2:J$47000<>"jjj222"),--(D$2:D$47000>E$2:E$47000))>0,D2>E2,J2<>"jjj222"),"Review","No Review Necessary")

Note that the placement of the $ are very important. You could also use named ranges in place of the large range references.