Highlight according to multiple criteria

[Rage]

Hi all,


I need some help creating a rule to highlight cells that correspond to some criterias.


Column A = House numbers ( It has several numbers and some are duplicated )
Column B = Date of Arrival
Column C = Date of Departure

What i wanted to do is to create a rule that if the same House has:

Departure ( Date on Column C ) is equal to the Arrival ( Date on Column B ) are equal it highlights the House Number ( Column A ) cell corresponding to the Arrival Date.


Example:

Column A / Column B / Column C
House / Arrival date / Departure date

1 - 20-3-2010 - 24-3-2010
1 - 24-3-2010 - 29-3-2010
4 - 20-4-2010 - 25-4-2010
12 - 12-3-2010 - 16-3-2010


Meaning that it would highlight those above in red

[NBVC]

Assuming you are starting in Row2, select Column A to B range and apply conditional formula:

=COUNTIFS($A$2:$A2,$A2,$C$2:$C2,$B2)

[Rage]

Assuming you are starting in Row2, select Column A to B range and apply conditional formula:

=COUNTIFS($A$2:$A2,$A2,$C$2:$C2,$B2)

Hi NBVC, i cant seem to make this work:

I have attached a list so that you may be able to help me.


You may notice that i've marked with and " x " in the adjacent columns the houses that meet the criterias below:


PS: Want i want is that it only highlights the row if the following conditions are met:

1 - Its the same house number and the departure date is the same as the next arrival date for the same house

2 - Its the same house number and the departure date is one day before the next arrival date for the same house


I hope that i'm not being a pain in the...

Cheers

[NBVC]

I am assuming you want to different conditions so that you have 2 different patterns based on those separate conditions...

For condition 1, (Its the same house number and the departure date is one day before the next arrival date for the same house) after selecting the entire range, select Use formula to determine which cells to format and enter:

=OR(COUNTIFS($A$10:$A$35,$A10,$C$10:$C$35,$B10-1),COUNTIFS($A$10:$A$35,$A10,$B$10:$B$35,$C10+1))

select your formats.

For condition 2 (Its the same house number and the departure date is the same as the next arrival date for the same house
):

=OR(COUNTIFS($A$10:$A$35,$A10,$C$10:$C$35,$B10),COUNTIFS($A$10:$A$35,$A10,$B$10:$B$35,$C10))

[Rage]

Thanks NBVC,

As always you ROCK :P

+1 For your fast help

[Rage]

To the MOD's, for some reason i can't edit the first post to mark as SOLVED.

Can you please do this. Thanks

[teylyn]

"some reason" is that you started the thread more than 2 days ago.

Marked solved.