CONDITIONAL FORMATTING - Multiple values in cells separated by commas

[UniqueUsername]

Hi Folks,

I'm looking to create a conditional formatting rule that can detect duplicated values in a column where a cell can contain multiple values separated by commas. I am trying to avoid using multiple columns to have one value per column due to space constraints

For example, if I had the following data in separate rows:
APPLE,GRAPE,PEAR,
BANANA,STRAWBERRY,ORANGE,
APPLE,
BLUEBERRY,PEAR,

Rows 1, 3 and 4 would be highlighted due to APPLE in row 1 and 3, and also PEAR in 1 and 4. Is this possible?

Thank you in advance for any help provided!

 

[coulombevin]

Hi @UniqueUsername,

Select the column where the results are (ex: A:A), in conditional formating, select custom formula and put this:

=SUM(IFERROR(MATCH("*"&TEXTSPLIT($A1,",",,TRUE)&",*",FILTER($A:$A,ROW()<>ROW($A:$A),""),0),0))>0

Change all $A by the column letter for your case. if you select a range instead of a whole column (ex: A2:A10) then you must change the 1 from $A1 to the first row of yours ($A1 would become $A2).

Bests regards,

Vincent

 

[UniqueUsername]

Worked like a charm, thank you!

 

[Peter_SSs]

Select the column where the results are (ex: A:A), in conditional formating, select custom formula and put this:

Hi Vincent (& @UniqueUsername)
A few comments

• For me, applying that to a whole column makes my worksheet very slow to respond. I have modified it to 10 rows below.
• It does not hurt but there is no need to test for the sum to be >0 since a zero sum will result in a FALSE CF test anyway.
• That CF fails if a circumstance like below occurs.

25 03 23.xlsm
	A
1	APPLE,GRAPE,
2	BANANA,STRAWBERRY,ORANGE,
3	PINEAPPLE,
4	BLUEBERRY,PEAR,
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=SUM(IFERROR(MATCH("*"&TEXTSPLIT($A1,",",,TRUE)&",*",FILTER($A$1:$A$10,ROW()<>ROW($A$1:$A$10),""),0),0))>0	text	NO

My suggestion is a slightly shorter modification.
(I am assuming all cells with data end with a comma as per the sample data.)

25 03 23.xlsm
	A
1	APPLE,GRAPE,PEAR,
2	BANANA,STRAWBERRY,ORANGE,
3	PINEAPPLE,
4	BLUEBERRY,PEAR,
5	APPLE,
6
7
8
9
10
CF (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=COUNT(MATCH("*,"&TEXTSPLIT(A1,",")&",*",FILTER(","&A$1:A$10,ROW()<>ROW(A$1:A$10),""),0))	text	NO