Help: Highlighting Only the First Duplicate

[Daniel Edgerton]

I have two columns of data. I'm comfortable with conditional logic and I've made the data consistent between the two columns so that highlighting duplicates is error-proof.

Here's where it gets bumpy: In Column B, I only need to highlight the FIRST instance of a duplicate value that exists in Column A. If that doesn't make sense, I don't know of any other way to explain it.

Current Situation:

Current Situation

Desired Outcome:

Desired Outcome

ALTERNATE SOLUTION:

I'll also accept the inverse scenario as it could be easy to filter: In Column B, highlight any duplicate (beyond the first) of a value that exists in Column A.

[#2]

@Daniel Edgerton 

You may apply conditional formatting rule like

[#3 · Accepted solution]

@Daniel Edgerton 

Select B1:B9 (or to the end of the data). B1 should be the active cell in the selection.

Create a rule of type 'Use a formula to determine which cells to format' with formula

=AND(ISNUMBER(MATCH(B1,A:A,0)),COUNTIF(B$1:B1,B1)=1)

[#4]

@HansVogelaar  - I tested it with the sample text, and it works. But I'm having difficulty applying it to my project. The columns contain email addresses along with some appended (concatenated) text to serve as a unique identifier.

Originally, I had an asterisk between the date (first 8 characters) and the email. I thought it might be interfering, so I replaced it with "ZZZ" and can remove it later. I then thought the "@" was the problem, so I replaced that with "XXX" and can remove that later. Regardless, the formula you suggested should work here, right? It doesn't, and I can't figure out why not. 

In writing this, I also replace the period in ".com" just to see. Nothing. Still doesn't work. It's just text, so why isn't it being recognized as a match? 

[#5]

@Daniel Edgerton 

Could you attach a sample workbook, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#6]

https://mygsu-my.sharepoint.com/:x:/g/personal/dedgerton1_gsu_edu/EUpGQvA9PwJFqvsJZT5YyPwBAFzKdtiuR_...

[#7]

Weird. It's working now. I have no idea what's stopping it elsewhere.

[#8]

@Daniel Edgerton 

Thanks! This is what I see when I download and open your workbook:

The conditional formatting rule appears to work as intended.

[#9]

Again, this is just sample text. The working file has 328 rows in Column A and 631 in Column B. No other changes to the formula, and still nothing is highlighting.

[#10]

@Daniel Edgerton 

At least in your sample rule is applied only to the range B1:B161, not to entire range

[#11]

Corrected. I was actively expanding the data to a range that matched my working file.

[#12]

@Daniel Edgerton 

So, now it works?

[#13]

It seems to work in the sample file. Can't get it to work in the real one.

[#14]

It's working now. I found the error. Thank you both!

[#15]

@Daniel Edgerton 

Great to know you sorted this out.

[#16]

@Daniel Edgerton 

Just for the sake of it, a slightly different approach to identifying the first occurrence of each string.

Because CF is a pile of antiquated functionality, one is forced to use relative referencing rather than array formulas.  First I provided a name for the current cell, which I show in R1C1 notation whereas A1 notation would give me any one of 17 billion ways of writing the same thing.

currentCell = Sheet1!RC;

firstInstance? 
= ISREF(
     currentCell  XLOOKUP(currentCell, sessions, sessions)
  );

The Boolean Named Formula firstInstance? looks up the current cell value in the list of sessions and returns a reference to the first occurrence.  If that reference and the current cell are one and the same, the argument of ISREF( ) will be the reference, otherwise a NULL error, giving TRUE/FALSE.  The formatting depends upon firstInstance?

[#17]

@HansVogelaar 

I have tried all of the formulas but none of them are working. I am working with dates, does that make a difference?

[#18]

@ginag2470 

Bu sure you correctly use first cell of the range to apply in formula, in general it shall work

Here first of duplicated values is highlighted and any value without duplicates.

[#19]

@SergeiBaklan 

That worked perfectly. Now is there a way to highlight the first of any duplicates in a column? In this spread sheet there are groups of rows that have #s that I need to highlight the first of each new group of duplicate numbers. The last calculation does not work as it only hight lights the first # that is a duplicate, not the first of the group of duplicates. if that makes sense.

 

[#20]

@ginag2470 

Select K2:K100 or however far down as necessary. K2 should be the active cell in the selection.

Create a rule with formula

=AND(K2<>K1, K2=K3)