Forum Discussion

DanielAtGSU's avatar
DanielAtGSU
Copper Contributor
Apr 15, 2022

Help: Highlighting Only the First Duplicate

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 b...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 15, 2022

    DanielAtGSU 

    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)

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 18, 2022

DanielAtGSU 

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?

 

Resources