Conditional formatting to find duplicate entries not working

[Whizbang]

Ok, I have a courier time tracking sheet that i used to evaluate courier times. One of the features is to compare the inputed time against a benchmark time. To do this I have a range of travel segments and their benchmark times.

I have formatted these "travel segments" as follows.

City1 <-> City2

The "<->" signafies either to or from.

So, what I want is conditional formatting that lets me know if a travel segment has already been entered for the two cities, even if transposed as "City2 <-> City1"

The formula I have worked out is:

=OR(MATCH(A2,A:A,0)<> ROW(A2), ISNUMBER(MATCH(RIGHT(A2,LEN(A2)-FIND(" <-> ",A2)-4) & " <-> " & LEFT(A2,FIND(" <-> ",A2)-1),INDIRECT("A1:A" & ROW(A2)-1),0)))

This works just fine when I have this formula in a cell, but when I enter it as a conditional format, it doesn't work.

Attached is an example workbook.

[NBVC]

I replaced the INDIRECT with the INDEX equivalent and it works:

Please Login or Register  to view this content.

[martindwilson]

if seen this before with OR but i dont know why!

[Whizbang]

Thanks, NBVC.

The other solution I found was

=OR(MATCH(A2,A:A,0)<>ROW(A2),IF(ISERROR(MATCH(RIGHT(A2,LEN(A2)-FIND(" <-> ",A2)-4)&" <-> "&LEFT(A2,FIND(" <-> ",A2)-1),A:A,0)<> ROW(A2))=FALSE,MATCH(RIGHT(A2,LEN(A2)-FIND(" <-> ",A2)-4)&" <-> "&LEFT(A2,FIND(" <-> ",A2)-1),A:A,0)<> ROW(A2)))

But I like your mutch better because there is no if statement to double the processing.

What's funny to me is that when I break the original formula apart, it works just fine.

If I enter =ISNUMBER(MATCH(RIGHT(A2,LEN(A2)-FIND(" <-> ",A2)-4) & " <-> " & LEFT(A2,FIND(" <-> ",A2)-1),INDIRECT("A1:A" & ROW(A2)-1),0)), it calculates just fine. It is only when I make it an OR that it stops.