Find missing dates

[jackt05]

Hi,

I have two columns as follows

Dates Cab Nos.

1-Jan 4501
2-Jan 4501
3-Jan 4501
5-Jan 4501
1-Jan 8409
2-Jan 8409
5-Jan 8409

there are more than 4000 entries with different cab nos for the whole month. Here 4501 cab is absent on the 4th of Jan and cab 8409 is absent on the 3rd and 4th Jan. I need to find out the dates on which the particular cab is absent I have about fifty different cabs.

Please help with any formula, I am not professional with excel.

Thank You

 

[Domenic]

Assumptions:

A2:B8 contains the data

D3 contains the first day of January  ----->  1/1/2009

E3 contains the last day of January  ----->   1/31/2009

F1 contains 4501

G1 contains 8409

Formulas:

F3, confirmed with CONTROL+SHIFT+ENTER, and copied across:

=SUM(IF(ISNA(MATCH(ROW(INDIRECT($D$3&":"&$E$3)),IF($B$2:$B$8=F$1,$A$2:$A$8),0)),1))

F4, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(F$4:F4)<=F$3,SMALL(IF(ISNA(MATCH(ROW(INDIRECT($D$3&":"&$E$3)),IF($B$2:$B$8=F$1,$A$2:$A$8),0)),ROW(INDIRECT($D$3&":"&$E$3))),ROWS(F$4:F4)),"")

Hope this helps!

 

[jackt05]

Thanks Domenic,

I copied our formula, what should the result be, its coming 1 & some where 0

which date is missing please if you wish give in detail

Thanks once again

 

[Domenic]

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula. Does this help?