Find And Highlight

Hi guys,

I've been cracking my brains on this for days, but I haven't got a solution.

I need to find the texts in cells and highlight them if they duplicate.

Say I have cells A1 to A4 with the texts: AJC1, AJC2, ACJC-1, AJC3 respectively. I need Excel to highlight cells A1, A2 and A3 because "AJC" appeared 3 times in the A1:A4 array.

Any solutions? Thanks!

=SUMPRODUCT(--(LEFT(A1:A4,3)="AJC")) gives a count of 3.

You could perhaps use this with Conditional Formatting to highlight the cells.

Regards

I'm struggling with what you're trying to achieve. Please post a sample workbook with some typical data.

Regards

See attached example workbook.


Regards

This caters for blank cells in the range:

=AND($A1<>"",SUMPRODUCT(--(LEFT($A$1:$A$18,3)=LEFT($A1,3)))>=3)


Regards

SUMPRODUCT is creating an array of the elements in the range A1 to A18 and then comparing them to the value in A1, effectively producing a count of the cells satisfying the condition. The -- is coercing the result into a numeric value.

Notice that the array is being compared to $A1 which means the row number will adjust for each row comparison, whereas the range is absolute and doesn't change. Actually, the $A is not essential in this case but it would allow you to apply the CF to other cells on the row.

So what you're getting is individual comparisons

=AND($A1<>"",SUMPRODUCT(--(LEFT($A$1:$A$18,3)=LEFT($A1,3)))>=3)
=AND($A2<>"",SUMPRODUCT(--(LEFT($A$1:$A$18,3)=LEFT($A2,3)))>=3)
:
=AND($A18<>"",SUMPRODUCT(--(LEFT($A$1:$A$18,3)=LEFT($A18,3)))>=3)


Regards

You're welcome.

If this answers your question, please mark your thread as solved. See my signature for details or the FAQ.

Regards