+ Reply to Thread
Results 1 to 7 of 7

How to find non-repeating values in Column B for repeating values in Column A

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    17

    Question How to find non-repeating values in Column B for repeating values in Column A

    I have a spreadsheet with 2 columns. Column A contains a list of names that may or may not repeat and Column B depicting favorite colors. If a value in Column A repeats, I want to select/highlight/identify/indicate all rows where the Column B values for that name are NOT the same.

    Here's the table:

    Name Color
    John Blue
    John Blue
    John Blue
    Harry Yellow
    Peter Red
    Peter Red
    Peter Blue
    Peter Green

    In this table, I would like to select/highlight/identify/indicate all the PETER rows. Is this possible? I am using Excel 2013.

    Thank you in advance.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,894

    Re: How to find non-repeating values in Column B for repeating values in Column A

    Assuming the word 'Name' is in cell A1 and 'Green' is in cell B9, try the following formula as a conditional formatting rule:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The rule needs to apply to the range A2:B9
    Let us know if you have any questions.
    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
    09-12-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: How to find non-repeating values in Column B for repeating values in Column A

    Hi JeteMc

    Thank you for your reply...it was very close!

    It is highlighting HARRY's single row and only the first 3 rows for Peter. If I include more than 1 row for HARRY, and change the formula to "=COUNTIFS($A$2:$A$10,$A2)<>COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2)", it works. There seems to be an issue if there's only a single row for a particular name, as is the case for HARRY.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,894

    Re: How to find non-repeating values in Column B for repeating values in Column A

    Here is a file that shows my results, the four rows with Peter.
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-12-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: How to find non-repeating values in Column B for repeating values in Column A

    Hi JeteMc

    Got it to work. Fat-fingering on my part. Very slow, however, as the actual spreadsheet has 14 columns and 63,000 rows. Still, only 2 columns are being compared.

    Thanks for your help!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,894

    Re: How to find non-repeating values in Column B for repeating values in Column A

    You're Welcome and thank you for the feedback.
    With that many columns and rows, it may be faster to use a helper column, which could be hidden for aesthetic purposes. The helper column would have the formula and the conditional formatting rule would simply reference the value in the corresponding row of the helper column. I am including a file to demonstrate.
    You may also want to ask someone on the VBA forum if they can write some code that would do this faster. If you decide to do that, I would suggest starting a new thread on that forum (after marking this one 'Solved' of course) and uploading a large enough file (with the CF formula applied) to give an indication of how slowly the formula is working. I would use a title something like 'Looking for VBA to speed conditional formatting applied to large range'. To be honest I know so little about VBA that I am not sure whether or not it can be done.
    I hope that you have a blessed day.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-12-2012
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: How to find non-repeating values in Column B for repeating values in Column A

    Hi JeteMc

    Even creating the Helper column is taking a lot of time to process because it's looking at the whole range for each row... =COUNTIFS($A$2:$A$63000,$A2)<>COUNTIFS($A$2:$A$63000,$A2,$B$2:$B$63000,$B2).

    I REALLY appreciate your help with this. I do get the desired result, albeit while I take a coffee break :-), but it works. I'll work up another VBA forum version of the question at your suggestion.

    Thanks again!

+ 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. Macro to find group of 5 similar values repeating same in one column
    By H_123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2016, 02:51 PM
  2. [SOLVED] Add a set series of numbers to a column of repeating values
    By Groovicles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-14-2013, 10:13 AM
  3. Copy Block of Values Repeating 1 Column's Value
    By sae2003us in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2012, 12:18 PM
  4. creating a repeating formula that doesn't show repeating values
    By cybershot in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-09-2010, 06:12 PM
  5. Replies: 6
    Last Post: 10-25-2009, 07:21 AM
  6. Group Repeating Text Values in a Column?
    By ConfusedNHouston in forum Excel General
    Replies: 1
    Last Post: 04-25-2006, 11:10 AM
  7. Replies: 1
    Last Post: 01-13-2005, 03:38 PM

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