SOLVED

Need help for Conditional Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-1508390%22%20slang%3D%22en-US%22%3ENeed%20help%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508390%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20school%20bus%20list%20that%20contains%20student's%20name%20and%20Phone%20numbers.%20Only%20one%20cell%20contains%20all%20these%20informations%20for%20a%20student.%20I%20want%20to%20use%20conditional%20formatting%20method%20for%20duplicated%20names.%20My%20problem%20is%2C%20sometimes%20I%20add%20some%20notes%20or%20etc%20under%20the%20names%2Fphone%20numbers%20and%20because%20of%20that%20conditional%20formatting%20doesn't%20work.%20Here's%20a%20demo%20screenshot%20that%20determines%20my%20problem%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20sample%2C%20JAMES%20can%20be%20formatted%20but%20WILLIAM%20can%20not%20be%20formatted%20because%20of%20the%20note%20(Red%20Text).%20how%20can%20I%20do%20it%20for%20all%20my%20list%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1508390%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1508594%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508594%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F721962%22%20target%3D%22_blank%22%3E%40Tollyver%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20making%20a%20New%20Formula%20for%20conditional%20formatting%20using%20the%20formula%20given%20below%20to%20do%20so%20follow%20these%20steps...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3ESelect%20your%20data%20range%20starting%20from%20cell%20A2%20e.g.%20as%20per%20your%20sample%20data%2C%20select%20the%20range%20A2%3AB6.%3C%2FLI%3E%3CLI%3E%3CSTRONG%3EHome%20Tab%3C%2FSTRONG%3E%20--%26gt%3B%20%3CSTRONG%3EConditional%20Formatting%3C%2FSTRONG%3E%20--%26gt%3B%20choose%20%3CSTRONG%3ENew%20Rule%3C%2FSTRONG%3E%20and%20in%20the%20next%20window%20select%20%22%3CSTRONG%3EUse%20a%20formula%20to%20determine%20which%20cells%20to%20format%3C%2FSTRONG%3E%22.%3C%2FLI%3E%3CLI%3EIn%20this%20window%20itself%2C%20in%20the%20InputBox%20which%20is%20just%20below%20the%20line%20which%20says%20%22%3CSTRONG%3Eformat%20values%20where%20this%20formula%20is%20true%3A%3C%2FSTRONG%3E%22%2C%20paste%20the%20formula%20given%20below.%3C%2FLI%3E%3CLI%3ENow%20click%20on%20Format%20and%20set%20the%20format%20as%20per%20your%20choice.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CU%3E%3CSTRONG%3EConditional%20Formatting%20Formula%3A%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIF(%24A%3A%24B%2CA2)%26gt%3B1%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1508779%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F721962%22%20target%3D%22_blank%22%3E%40Tollyver%3C%2FA%3E%26nbsp%3B%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20modify%20a%20bit.%20If%20we%20shall%20exclude%20notes%20let%20consider%20as%20duplicates%20only%20cells%20where%20first%20two%20lines%20of%20text%20are%20the%20same.%20When%20we%20may%20apply%20conditional%20formatting%20rules%20to%20entire%20columns%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20610px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F204097i44F933E64A0C5DE4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewith%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D(A1%26lt%3B%26gt%3B%22%22)*COUNTIF(%24A%3A%24B%2CLEFT(A1%2CIFERROR(FIND(CHAR(10)%2CA1%2CFIND(CHAR(10)%2CA1)%2B1)-1%2CLEN(A1)))%26amp%3B%22*%22)%26gt%3B1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%20similar%20if%20we%20take%20another%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1508885%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1508885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthanks!%20That's%20worked%20for%20me.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1509355%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20for%20Conditional%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1509355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F721962%22%20target%3D%22_blank%22%3E%40Tollyver%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

4 Replies

@Tollyver 

Try making a New Formula for conditional formatting using the formula given below to do so follow these steps...

 

  1. Select your data range starting from cell A2 e.g. as per your sample data, select the range A2:B6.
  2. Home Tab --> Conditional Formatting --> choose New Rule and in the next window select "Use a formula to determine which cells to format".
  3. 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.
  4. Now click on Format and set the format as per your choice.

Conditional Formatting Formula:

=COUNTIF($A:$B,A2)>1

 

Best Response confirmed by Tollyver (New Contributor)
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

image.png

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.

@Sergei Baklan thanks! That's worked for me.

@Tollyver , you are welcome