How to alert when input duplicate specific name on the same date?

I want to make a schedule, three persons for a day. But one person just have one day on the same date.
How to make an alert or validating if there is a duplicate specific name on the same date. Every name on the cell is separated by semicolon (.
Look at the picture below:

duplicate.jpg

Personally I would do this with VBA code which is more flexible and would be more compact to maintain. However if you want to do it on the sheet, here is an answer using helper columns. I didn't combine any of them, yet, which I just about always find to be the best approach when building long formulas from scratch. You can fold columns C:D into E:G, and then fold those into H:Q; you'll have less cells, though it will be visually more non-intuitive. You can also hide every column other than A:B and the last one, or you can move the others out of view or onto another sheet, even a hidden one.
C2=SEARCH(";",$B2,1)
D2=SEARCH(";",$B2,C2+1)
E2=$A2 & "|" & TRIM(LEFT($B2,C2-1))
F2=$A2 & "|" & TRIM(MID($B2,C2+1,D2-C2-1))
G2=$A2 & "|" & TRIM(MID($B2,D2+1,999))
H2=COUNTIF(E:E,E2)
I2=COUNTIF(F:F,F2)
J2=COUNTIF(G:G,G2)
K2=COUNTIF(E:E,F2)
L2=COUNTIF(F:F,G2)
M2=COUNTIF(G:G,E2)
N2=COUNTIF(E:E,G2)
O2=COUNTIF(F:F,E2)
P2=COUNTIF(G:G,F2)
Q2=COUNTIF(H2:P2,">1")
R2=IF(Q2>0,"DUPLICATE FOUND","ok")

Then copy C2:R2 all the way down.

This relies on there always being exactly 3 names, always separated by semicolons ";". If any of that is untrue this would need modification.

Note the above is based on the data starting in row 2, not row 1; adjust appropriately.

Simple UDF

Please Login or Register  to view this content.

With data in column A row 2 down

in B2 =Check_Duplicates(A2)

Copy down

John the code shown as of edit time 1:23 PM (here) does not do what he asked for. For one thing it only checks the same row and it does nothing with date.

At any rate, I did not use VBA though I said that I believed it was preferable because he put this question in the "formula and functions" section, though there would be no harm in you posting a good code solution. If you want to do that, I think you'll want to read the entire input into an array and work from "stored memory" rather than cells. The other route is with .Find which I gather is more efficient than most VBA operations.

VBA updated:

Please Login or Register  to view this content.

I didn't check the code or attachment but they appear to require that the input be sorted so check that. The formulas in my post tediously and exhaustively check every combination if that helps (if the data is not guaranteed to be sorted). (Of course that can be compactly done in code, too, which would be my choice.)

Worked OK for me. Post file which does not work.

It assumes file is SORTED by date as pointed out earlier by Oppressed1