Hello!

I have two sheets, one is a diagram of peoples jobs the other is a list of names against job numbers.

Using the below formula the jobs are populated by the names of the workers, however there are duplicates (more workers than jobs).
=IF(ISNA(VLOOKUP(R36,'Nom Roll'!$I$3:$K$65536,3,FALSE)),"VACANT",VLOOKUP(R36 ,'Nom Roll'!$I$3:$K$65536,3,FALSE))

I need a bit of code that will trawl the list identify duplicates, put the duplicate names into a comment on the cell with the above formula and finaly turn the cell red.

Sorry if its a bit basic, anyone have any ideas?
Thanks in advance.
Mike