Slightly cleverer way to check for duplicates

[jimmyd270682]

Excel has a very clever way to highlight two cells with conditional formatting. I need to try and do the same thing in our work schedule to highlight two cells even if there is two names in one of the cell which is the same as one name in another. For example with columns set out

Date, Task 1, Vehicle x, Task 2, Vehicle, Task 3, Vehicle y, Task 4, Vehicle z,
01/01/2016, John, London, John/Gary, Eltham, Brian, Nottingham, Terry, Glasgow,


I have names ie John or John/Gary in the task columns, there are so many tasks that it's becoming difficult to see if someone has already been put down for a job, but sometimes we put two names in, so the two cells need to turn red (or something) to highlight that John, for example, is already down for a job and therefore can't also be going with Gary on another job!

Has anyone ever tried something like this?

Thanks for any help

Jimmy

[Richard Buttrey]

You'd simplify your analysis considerably and be able to achieve your aim if you were to keep your data in a normalised two dimensional data table where each column is a specific TYPE of information. So for instance in your case you'd have columns for

Date
Task
Vehicle
Name

and each row would be a unique record. Then a simple Pivot Table would draw out where there were duplicate jobs for a particular person on the same day.

[FlameRetired]

Withdrawn by FR

[jimmyd270682]

The primary objective for the schedule is an easy to view schedule, the vehicle column has is fact got the reg number of that vehicle as the title, so its not necessary to have a column for that. This would just be a great extra feature. Putting the data into a pivot table was never something that was considered and in any case, there's a new tab for each month and we use it to plan months in advance

[jimmyd270682]

Basically all I need to be doing is highlight two cells if they contain the same word in a row, surely that's possible?

[Richard Buttrey]

Basically all I need to be doing is highlight two cells if they contain the same word in a row, surely that's possible?

Yes, with a pivot table as I suggested. For which you need the layout I suggested.

[jimmyd270682]

Indeed, however i'm not able to keep the more important aim of it being a schedule as opposed to a data table if I keep the names in a normalised two dimensional data table. It would be worse as a schedule, which is more important than what i've been trying to add. Thanks for the tip anyway!