Conditional Formatting

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Conditional Formatting

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional Formatting

     
    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

  2. #2
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,408
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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


    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","")
    Last edited by Scott T; Jul 17th, 2017 at 08:24 AM.
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  3. #3
    New Member
    Join Date
    Sep 2015
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Coditional Formatting

    Quote Originally Posted by Scott T View Post
    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


    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","")


    That worked a treat, thanks v much

  4. #4
    New Member
    Join Date
    Sep 2015
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Coditional Formatting

    Quote Originally Posted by Scott T View Post
    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


    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","")
    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

  5. #5
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,408
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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","")
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  6. #6
    New Member
    Join Date
    Sep 2015
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Coditional Formatting

    Quote Originally Posted by Scott T View Post
    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","")
    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

  7. #7
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    1,408
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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)
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com