Forum Discussion
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 duplicated names. My problem is, sometimes I add some notes or etc under the names/phone numbers and because of that conditional formatting doesn't work. Here's a demo screenshot that determines my problem:
In this sample, JAMES can be formatted but WILLIAM can not be formatted because of the note (Red Text). how can I do it for all my list?
Thanks
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)))&"*")>1or similar if we take another range.
4 Replies
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)))&"*")>1or similar if we take another range.
SergeiBaklan thanks! That's worked for me.
Tollyver , you are welcome
Try making a New Formula for conditional formatting using the formula given below to do so follow these steps...
- Select your data range starting from cell A2 e.g. as per your sample data, select the range A2:B6.
- Home Tab --> Conditional Formatting --> choose New Rule and in the next window select "Use a formula to determine which cells to format".
- In this window itself, in the InputBox which is just below the line which says "format values where this formula is true:", paste the formula given below.
- Now click on Format and set the format as per your choice.
Conditional Formatting Formula:
=COUNTIF($A:$B,A2)>1
