Forum Discussion

Tollyver's avatar
Tollyver
Copper Contributor
Jul 08, 2020
Solved

Need help for Conditional Formatting

Hi everyone;   I have a school bus list that contains student's name and Phone numbers. Only one cell contains all these informations for a student. I want to use conditional formatting method for ...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 08, 2020

    Tollyver , Subodh_Tiwari_sktneer 

    I'd modify a bit. If we shall exclude notes let consider as duplicates only cells where first two lines of text are the same. When we may apply conditional formatting rules to entire columns

    with formula

    =(A1<>"")*COUNTIF($A:$B,LEFT(A1,IFERROR(FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)-1,LEN(A1)))&"*")>1

    or similar if we take another range.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 08, 2020

Tollyver , Subodh_Tiwari_sktneer 

I'd modify a bit. If we shall exclude notes let consider as duplicates only cells where first two lines of text are the same. When we may apply conditional formatting rules to entire columns

with formula

=(A1<>"")*COUNTIF($A:$B,LEFT(A1,IFERROR(FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)-1,LEN(A1)))&"*")>1

or similar if we take another range.

Resources