Part Matching Duplicate Values

hi,

hope someone can help. Desperately need to do part matching similar to this scenario:

ColA
there was a dog that jumped over
there was a dog that jumped over the fence

Everything is in ColA. We are on excel 2007. We need to highlight these two are duplicates based on matching the first 40 characters.

Please help!

Select the range and go to Format|Conditional Formatting

Select Formula Is from 1st drop down and enter a formula like this:

=SUMPRODUCT(--(LEFT($E$1:$E$2,40)=LEFT($E1,40)))>1

where E1:E2 are the cells you selected and E1 is the first cell in that range.

Change to suit.

Click Format and choose colour from Pattern Tab.

Click Ok

Click Ok to finish.

thanks for reply. not working for me so am missing something....have done:

1) highlight all Column A
2) Conditional formatting
2) More rules
3) Enter a formula to determine what cells to format
4) selected a colour.
5) Formula is

=SUMPRODUCT(--(LEFT($A$1:$A$2,40)=LEFT($A2,40)))>1

All my data is in column A. First row is a header. Second row is begin of data.

?

=SUMPRODUCT(--(LEFT($A$2:$A$2,40)=LEFT($A2,40)))>1

The reference in red above should be the last occupied cell in the column that you have selected.

The reference in Blue should be the first/top cell selected

You should just select the actual used range, not the whole column