Hello,
I have been asked to come up with a formula to highlight duplicate names in a people tracker sheet, to ensure the same person can't be against more than one project on the same day, basically the sheet is used to track personnel on projects, see my very small extract below where you can see "Person 4" has been selected against two different projects on the same day for several days.
I can obviously use the conditional formatting tool to highlight duplicate names in each column, however I would also like a quick reference along row 2 (highlighted in red) which if there is a duplicate the word "yes" would appear. What would be the formula for this row. Please bear in mind there are over 100 names that can be selected for any project on any day which is why the spreadsheet is getting out of control with the same person being forecast against more than one project on the same day.
So bearing in mind that there are over 100 names which can be used, can the formula identify duplicates without referring to the unique list of possible names that can be selected?
The reason I even want this quick reference line is because there are numerous projects detailed on this spreadsheet each day and therefore the volume of rows if large more than can be viewed without scrolling far down the page.
Any help would be grateful, thanks
<tbody>
</tbody>
I have been asked to come up with a formula to highlight duplicate names in a people tracker sheet, to ensure the same person can't be against more than one project on the same day, basically the sheet is used to track personnel on projects, see my very small extract below where you can see "Person 4" has been selected against two different projects on the same day for several days.
I can obviously use the conditional formatting tool to highlight duplicate names in each column, however I would also like a quick reference along row 2 (highlighted in red) which if there is a duplicate the word "yes" would appear. What would be the formula for this row. Please bear in mind there are over 100 names that can be selected for any project on any day which is why the spreadsheet is getting out of control with the same person being forecast against more than one project on the same day.
So bearing in mind that there are over 100 names which can be used, can the formula identify duplicates without referring to the unique list of possible names that can be selected?
The reason I even want this quick reference line is because there are numerous projects detailed on this spreadsheet each day and therefore the volume of rows if large more than can be viewed without scrolling far down the page.
Any help would be grateful, thanks
Duplicate | Yes | Yes | Yes | Yes | Yes | ||
W28 | W29 | W30 | W31 | W32 | |||
Region | Project / | Roles | 10-Jul | 17-Jul | 24-Jul | 31-Jul | 07-Aug |
EUR | Project 1 | ||||||
OS | Person 1 | Person 1 | Person 1 | Person 1 | Person 1 | ||
OS | Person 2 | Person 2 | Person 2 | Person 2 | Person 2 | ||
SE | Person 3 | Person 3 | Person 3 | Person 3 | Person 3 | ||
ME | Project 2 | ||||||
PC | Person 4 | Person 4 | Person 4 | Person 4 | Person 4 | ||
SS | Person 5 | Person 5 | Person 5 | Person 5 | Person 5 | ||
ROW | Project 3 | ||||||
OS | Person 6 | Person 6 | Person 6 | Person 6 | Person 6 | ||
OS | Person 4 | Person 4 | Person 4 | Person 4 | Person 4 | ||
SE | Person 7 | Person 7 | Person 7 | Person 7 | Person 7 |
<tbody>
</tbody>