Highlight duplicate based on a different columns contents

[spidervarient]

I'm still learning about a lot of the excel functions, but am stumped on how to do this.

We have an excel spreadsheet which on one sheet has a ODBC MS query pulling data into a sortable list.

On the second sheet we have a list of “outstanding payments” from the court, for cases not paid for.


What I’d like to do, is be able to highlight column E (or the row), on sheet 1 (last name), where an identical name is found in column B of sheet 2 (case name).
Since both columns are the last name of the person only, this SHOULD be able to match quite a few items, and highlight them.
Our ultimate goal is to be able to identify cases where the case name is the same, and we’ve already made/paid what the court feels to be an outstanding balance.
Ideally I’d like to make sure not to have to modify the layout of either sheet, as the business users still need to utilize the data extract on sheet 1 routinely.

any help would be great!

Thanks

[Tsjallie]

You can add conditional formating to column E of sheet 1.
Formula should look something like this

=IFERROR(MATCH($Ex,Sheet2!Bx:By,0),0)>0

$Ex is the cell holding the Lastname and Sheet2!Bx:By is the range in Sheet2 holding the case names.

[spidervarient]

I attempted to input the formula as you'd suggested

=IFERROR(MATCH(E:E,'Court PERS'!B:B,0),0)>0

Although it took the formula into the conditional format section without an error... NOTHING HAPPENED, nothing highlighted.
So now... I'm confused.

[spidervarient]

Also tried this coding, but it too fails as it only shows a single amount for EVERY SINGLE line, (attempting to show the "amount owed" for a particular case name)... so two fails in a row, with no ability to help the users reconcile.

=IFERROR(VLOOKUP(E1:E2311,'Court PERS'!A1:F1361,4),"Not Found")

[Tsjallie]

Can you upload your workbook (or sample) so I can have a look.

[spidervarient]

Here's a "sample" file with a majority of (sensitive information) removed... except the pertinent fields we'd discussed.

[Tsjallie]

We have an excel spreadsheet which on one sheet has a ODBC MS query pulling data into a sortable list.
On the second sheet we have a list of “outstanding payments” from the court, for cases not paid for.

Which of the two sheets holds the MS Query connection?

What I’d like to do, is be able to highlight column E (or the row), on sheet 1 (last name), where an identical name is found in column B of sheet 2 (case name).
Since both columns are the last name of the person only, this SHOULD be able to match quite a few items, and highlight them.

I think this is done with the conditional formatting. See attached workbook. Conditional formatting is in sheet 1 column C

Our ultimate goal is to be able to identify cases where the case name is the same, and we’ve already made/paid what the court feels to be an outstanding balance.

How would you identify payments related to outstanding balances?