Conditional Formatting

A

Abvlecxe

Board Regular
Joined
Sep 10, 2015
Messages
53
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


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>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Re: Coditional Formatting

A
B
C
D
E
F
G
H
I
1
Duplicate
Yes
Yes
Yes
Yes
Yes
2
3
W28
W29
W30
W31
W32
W32
4
Region
Project /
Roles
10-Jul
17-Jul
24-Jul
31-Jul
7-Aug
14-Aug
5
EUR
Project 1
6
OS
Person 1
Person 1
Person 1
Person 1
Person 1
Person 1
7
8
OS
Person 2
Person 2
Person 2
Person 2
Person 2
Person 2
9
10
SE
Person 3
Person 3
Person 3
Person 3
Person 3
Person 3
11
12
ME
Project 2
13
PC
Person 4
Person 4
Person 4
Person 4
Person 4
Person 4
14
15
SS
Person 5
Person 5
Person 5
Person 5
Person 5
Person 5
16
17
ROW
Project 3
18
OS
Person 6
Person 6
Person 6
Person 6
Person 6
Person 6
19
20
OS
Person 4
Person 4
Person 4
Person 4
Person 4
Person 8
21
22
SE
Person 7
Person 7
Person 7
Person 7
Person 7
Person 7

<tbody>
</tbody>


In D1 and copy across. For no duplicates I return blank. If you want to return something else replace the "" with what you want to return.
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula
Code: 
=IF(SUM(COUNTIF(D6:D22,D6:D22))>COUNTA(D$6:D$22),"Yes",[COLOR=#ff0000]""[/COLOR])
 
Last edited:
Upvote 0
Re: Coditional Formatting

Scott T said:
A
B
C
D
E
F
G
H
I
1
Duplicate
Yes
Yes
Yes
Yes
Yes
2
3
W28
W29
W30
W31
W32
W32
4
Region
Project /
Roles
10-Jul
17-Jul
24-Jul
31-Jul
7-Aug
14-Aug
5
EUR
Project 1
6
OS
Person 1
Person 1
Person 1
Person 1
Person 1
Person 1
7
8
OS
Person 2
Person 2
Person 2
Person 2
Person 2
Person 2
9
10
SE
Person 3
Person 3
Person 3
Person 3
Person 3
Person 3
11
12
ME
Project 2
13
PC
Person 4
Person 4
Person 4
Person 4
Person 4
Person 4
14
15
SS
Person 5
Person 5
Person 5
Person 5
Person 5
Person 5
16
17
ROW
Project 3
18
OS
Person 6
Person 6
Person 6
Person 6
Person 6
Person 6
19
20
OS
Person 4
Person 4
Person 4
Person 4
Person 4
Person 8
21
22
SE
Person 7
Person 7
Person 7
Person 7
Person 7
Person 7

<tbody>
</tbody>


In D1 and copy across. For no duplicates I return blank. If you want to return something else replace the "" with what you want to return.
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula
Code: 
=IF(SUM(COUNTIF(D6:D22,D6:D22))>COUNTA(D$6:D$22),"Yes",[COLOR=#ff0000]""[/COLOR])
Click to expand...



That worked a treat, thanks v much
 
Upvote 0
Re: Coditional Formatting

Scott T said:
A
B
C
D
E
F
G
H
I
1
Duplicate
Yes
Yes
Yes
Yes
Yes
2
3
W28
W29
W30
W31
W32
W32
4
Region
Project /
Roles
10-Jul
17-Jul
24-Jul
31-Jul
7-Aug
14-Aug
5
EUR
Project 1
6
OS
Person 1
Person 1
Person 1
Person 1
Person 1
Person 1
7
8
OS
Person 2
Person 2
Person 2
Person 2
Person 2
Person 2
9
10
SE
Person 3
Person 3
Person 3
Person 3
Person 3
Person 3
11
12
ME
Project 2
13
PC
Person 4
Person 4
Person 4
Person 4
Person 4
Person 4
14
15
SS
Person 5
Person 5
Person 5
Person 5
Person 5
Person 5
16
17
ROW
Project 3
18
OS
Person 6
Person 6
Person 6
Person 6
Person 6
Person 6
19
20
OS
Person 4
Person 4
Person 4
Person 4
Person 4
Person 8
21
22
SE
Person 7
Person 7
Person 7
Person 7
Person 7
Person 7

<tbody>
</tbody>


In D1 and copy across. For no duplicates I return blank. If you want to return something else replace the "" with what you want to return.
This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula
Code: 
=IF(SUM(COUNTIF(D6:D22,D6:D22))>COUNTA(D$6:D$22),"Yes",[COLOR=#ff0000]""[/COLOR])
Click to expand...

That worked a treat, thanks very much.

Is it possible to introduce an exception?!

When I put the formula into the actual spreadsheet multiple columns came back with "yes" which shows it's working....great, but the reason so may came back as yes is because as well as tracking the crew names they are also tracking things like visa applications against projects, so on some days "visa" is entered against multiple projects and therefore makes the formula come back "yes" where in reality I would like the formula to exclude the word "visa"

Thanks
 
Upvote 0
Re: Coditional Formatting

Try
As before you must use CONTROL+SHIFT+ENTER
Code: 
=IF(SUM(COUNTIFS(D6:D26,D6:D26,D6:D26,"<>"&"visa"))>COUNTA(D$6:D$26)-COUNTIF(D6:D26,"visa"),"Yes","")
 
Upvote 0
Re: Coditional Formatting

Scott T said:
Try
As before you must use CONTROL+SHIFT+ENTER
Code: 
=IF(SUM(COUNTIFS(D6:D26,D6:D26,D6:D26,"<>"&"visa"))>COUNTA(D$6:D$26)-COUNTIF(D6:D26,"visa"),"Yes","")
Click to expand...

Happy days, that worked thanks.

One last part, below the row which has the "yes" for duplicate I want to show the number of duplicates so for example if the name "Person 4" was in 2 times this would count as 1 duplicate etc

Thanks for your help



So
 
Upvote 0
Re: Coditional Formatting

Try this UDF
I believe that need to enable the Microsoft Scripting Runtime as shown in the comment in the start

Code: 
Function countdup(rng As Range)
    ' Select Tools->References from the Visual Basic menu.
    ' Check box beside "Microsoft Scripting Runtime" in the list.
    Dim dict As New Scripting.Dictionary
'Dim key As Variant
For Each cell In rng
    If dict.Exists(cell.Value) Then
        dict(cell.Value) = dict(cell.Value) + 1
    Else
        dict.Add (cell.Value), 1
    End If
Next cell
For i = 0 To dict.Count - 1
    If dict(dict.Keys(i)) > 1 And dict.Keys(i) <> "visa" And dict.Keys(i) <> "" Then
        dupcount = dupcount + 1
    End If
Next i
countdup = dupcount
End Function

to use in the cell you want the count in
Code: 
=countdup(D6:D22)
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Help on Xlookup
Replies
5
Views
147
Alex Blakenburg
A
S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
98
spenserallen7
S
Z
  • Question
Creating a report/list from a count
Replies
0
Views
56
Zeke247
Z
charlesstricklin
  • Question
How do I calculate a column based on two criteria?
2
Replies
11
Views
278
Joe4
Joe4
O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
387
KRice
K

Forum statistics

Threads
1,214,378
Messages
6,119,188
Members
448,873
Latest member
jacksonashleigh99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top