+ Reply to Thread
Results 1 to 4 of 4

Find duplicate values in a column, when both values have matching value in another

  1. #1
    Registered User
    Join Date
    11-30-2011
    Location
    Syracuse, NY
    MS-Off Ver
    MS 365
    Posts
    17

    Find duplicate values in a column, when both values have matching value in another

    I have a situation where I have various "projects" with varying numbers of team members assigned to each. Each of those members may be assigned software "options", but cannot share options, nor can they be assigned more than one.
    I need to be able to find and highlight any pair of duplicate values in my columns, but only if they both belong to the same "project" in another column.

    Conditional Formatting rows is easy, as they only need to have more than one duplicate value.
    Using =COUNTIF($D31:$G31,"<>")>1

    Columns get tricky because I expect duplicate values in the whole column, just can't occur in the same group of rows, (Identified by project # in another column.)
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: Find duplicate values in a column, when both values have matching value in another

    This will require two conditional formatting rules.
    The rule for rows (as you stated): =COUNTIFS($D23:$G23,"X")>1
    Note that if you only want the cells with X's highlighted you could use: =AND(D23="X",COUNTIFS($D23:$G23,"X")>1)
    The rule for columns could be: =AND(D23="X",COUNTIFS($B$23:$B$36,$B23,D$23:D$36,"X")>1)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    11-30-2011
    Location
    Syracuse, NY
    MS-Off Ver
    MS 365
    Posts
    17

    Re: Find duplicate values in a column, when both values have matching value in another

    Thanks, JeteMc. that works perfectly for truly "duplicate" values. Which I realize now is exactly what I asked, but what I was getting at is "TWO OR MORE NON-BLANK CELLS". My rows rule in my original post works fine, because it's only looking for any two or more non-blank cells in any row, thus the "<>", which is a single condition. The columns have to meet multiple conditions.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: Find duplicate values in a column, when both values have matching value in another

    Apply the following rule to the range D23:G36 =AND(D23<>"",COUNTIFS($B$23:$B$36,$B23,D$23:D$36,"<>")>1)
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find matching values then go to 3rd column of worksheet with duplicates and get that value
    By ButteredToast in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2018, 10:09 PM
  2. I need to club two column values and search with it to find duplicate values
    By Abhimanyu.bade in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 09-21-2018, 10:31 AM
  3. [SOLVED] Delete Duplicate Values by matching 2 sets of column
    By Ariff_Chowdhury in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2018, 03:17 PM
  4. Find duplicate values only when a value in another column is different
    By Simeonov in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-22-2018, 11:08 AM
  5. List unique values matching a criteria that has duplicate values
    By Andrewjs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2017, 10:11 PM
  6. How to find duplicate values in another column
    By JessK in forum Excel General
    Replies: 2
    Last Post: 06-16-2014, 09:11 AM
  7. How to find matching values in two column of data
    By medialiver in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-23-2013, 09:59 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1