Conditional formatting - highlight previous appearance

[smide]

Hello.


In column A (A3:A600) I have a customer names and in column B (B3:B600) customer's cities (their current location).


In cell C3 there is a 'referent city'. When customer is in 'referent city' I need to highlight his previous appearence (column A).


example.



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Customer[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]Ref. City[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]J.Cain[/TD]
[TD="align: center"]Rome[/TD]
[TD="align: center"]Vienna[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]L.Clarkson[/TD]
[TD="align: center"]London[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]B.Duncan[/TD]
[TD="align: center"]Berlin[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Y.Larouci[/TD]
[TD="align: center"]Melbourne[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]B.Duncan[/TD]
[TD="align: center"]Vienna[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]J.Cain[/TD]
[TD="align: center"]Amsterdam[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]L.Clarkson[/TD]
[TD="align: center"]Vienna[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


explanation: Referent city (cell C3) is Vienna. Customer B.Duncan was in Vienna (cells A7 and B7), so his previous appearance was in cell A5 - highlight cell A5.
Same story for L.Clarkson (A9 and B9) so highlight his previous appearance in cell A4.

 

[Special-K99]

Not saying I'll answer this but further information may help others.
I'm going to assume some things based on your example data so you need to clarify things.

Based on your data I'm assuming:

1. A Customer will only ever appear a maximum of 2 times in column A?
If this is not the case should ALL the customers previous entries be highlighted. e.g. suppose L Clarkson was in A10 at city Paris should that be highlighted as well?

2. The reference city will only ever appear in teh cell C3? There will be no other reference cities in other cells?
If that's not correct that could prove a very complicated solution.

 

[smide]

Based on your data I'm assuming:

1. A Customer will only ever appear a maximum of 2 times in column A?
If this is not the case should ALL the customers previous entries be highlighted. e.g. suppose L Clarkson was in A10 at city Paris should that be highlighted as well?

Customer could appear more than 2 times in column A (A3:A600). Not all previous entries, just one (last) previous entry should be highlighted.

2. The reference city will only ever appear in teh cell C3? There will be no other reference cities in other cells?
If that's not correct that could prove a very complicated solution.

Just one referent city in cell C3, no other cities.