Excel Column Compare

[dayaptl]

Hi,
I have four columns A, B, C, D
If the text of the any cell on D column appears in any of the three previous columns (A, B, C), That cell is to be highlighted.

I am using "=COUNTIF($A$1:$J$1000,A1)>1" with some formatting for my purpose.

Now my need is If only initial four character of any cell on D column appears in a same sequence in any of previous three columns should be highlighted.

Regards

[duane]

try this as an array formula somwhere and condition format cell d1 with condition that this cell >1

=SUMPRODUCT((NOT(ISERROR(SEARCH(LEFT(d1,4),A1:c100,1))))*1)

[Carim]

Hi,

A simple conditional formatting
Formula Is

Please Login or Register  to view this content.

Patterns Yellow

HTH
Carim

[Richard Schollar]

=AND(MATCH("*"&LEFT($D1,4)&"*",$A1:$C1,0),D1<>"")

which assumes that you are formatting col D cells