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

1. 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.

2. 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:
`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.

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

Hi JeteMc

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. 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.

5. 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.

6. 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.

7. 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!

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

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