Jul 08 2020 03:08 AM
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
Jul 08 2020 04:46 AM - edited Jul 08 2020 04:48 AM
Try making a New Formula for conditional formatting using the formula given below to do so follow these steps...
Conditional Formatting Formula:
=COUNTIF($A:$B,A2)>1
Jul 08 2020 06:16 AM
Solution@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.
Jul 08 2020 06:50 AM
@Sergei Baklan thanks! That's worked for me.
Jul 08 2020 06:16 AM
Solution@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.