Find Partial Duplicates

[LPred]

Is there any way to do a conditional formatting to highlight duplicates based on the second part of a cell? I have a multi column list of patient. They are entered as "Last Name,First Initial Room#" for instance "Smith,J 2317" right now I have to go through our system and manually check if a person has changed rooms. I have to do this for every person. I would like to find a way to have it highlight cells if the number matches. This way if someone comes in and is placed in a room that someone else is still listed in, I know to update that person.

 

[Aladin Akyurek]

How do we know what to color?

 

[LPred]

I would like to have cells that have the room# match be highlighted.

 

[Aladin Akyurek]

I would like to have cells that have the room# match be highlighted.

Match to what?

So let's have

Smith,J 2317
Goodann,L 2045
Urton,J 3245

what are we going to color?

 

[LPred]

So if we have:

Smith,J 2317
Goodann,L 2045
Urton,J 3245
Myers,P 2317

The entire cell with Smith and Myers would be highlighted, because the numbers are the same.

 

[Aladin Akyurek]

So if we have:

Smith,J 2317
Goodann,L 2045
Urton,J 3245
Myers,P 2317

The entire cell with Smith and Myers would be highlighted, because the numbers are the same.

Select A2:A5.
Fire up conditional formatting with the following formula:

=COUNTIFS($A$2:$A$5,"* "&RIGHT($A2,4))>1

And format as desired.

Note that this set up assumes a 4-digit number preceded by a space at the end of each entry.