Excel formula for conditional formatting > Duplicate values

Question

Hi, I have the following information in one column starting at cell F3 through to F1000:

Cell F3: 2000, 2067, 2062, 2004, 2896

Cell F4: 2067

Cell F5: 2117, 2846, 2896

Cell F6: 2020, 2763

etc ...

I need a formula using the conditional formatting > New Rule > Use a Formula to determine which cells to format function that works with cells containing both single entries (such as F4) & multiples separated by a comma & a space such as F3, F5 & F6. If there are duplicates across the data set, I just need the cell to shade yellow. In the sample above, cells F3, F4 & F5 would all end up shaded yellow as they contain duplicates. Please note I am using Excel version 2016 & I have to keep everything in one column (ie: no helper columns). Many thanks

Answer 1

Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.

Enter this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",99)),(ROW(INDIRECT("1:" & LEN(F3)-LEN(SUBSTITUTE(F3,",",""))+1))-1)*99+1,99)), $F$3:$F$1000)))>1

 

That should work in Excel 365 / Excel 2016.

My answers are voluntary and without guarantee!

 

Hope this will help you.

Answer 2

Hello,

You can do this with one Conditional Formatting rule, no helper columns, in Excel 2016. Select F3:F1000, then go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, paste the formula below, set a yellow fill, and confirm.

=SUMPRODUCT(--(ROW($F$3:$F$1000)<>ROW(F3)),
 --ISNUMBER(SEARCH(", "&TRANSPOSE(TRIM(MID(SUBSTITUTE(F3,", ",REPT(" ",99)),
 ROW($1:$99)*99-98,99)))&", ", ", "&$F$3:$F$1000&", ")),
 TRANSPOSE(--(TRIM(MID(SUBSTITUTE(F3,", ",REPT(" ",99)),
 ROW($1:$99)*99-98,99))<>"")))>0

This rule splits the active cell's list on the comma-space, checks each code as a whole token, and highlights the cell if any of its codes also appear anywhere else in F3:F1000. The 99 in the formula is the maximum number of codes per cell the rule will parse. If a single cell can contain more than 99 codes, increase that number. Make sure your entries are separated exactly by a comma and a space, for example 2000, 2067, 2062.

Answer 3

Hi,

This will work in Office365. Assign the name logic to this formula

=SUM(1*(BYCOL(1*(TOCOL(DROP(REDUCE("",$F$3:$F$6,LAMBDA(s,c,VSTACK(s,TEXTSPLIT(c,", ")))),1),3)=TEXTSPLIT(F3,", ")),SUM)>1))

In conditional formatting, just mention =logic.