This one is an array** formula. Entered in D1 then copied down as needed:

=IF(B1="A",MATCH(1,(B$1:B$9="B")*(C$1:C$9=C1),0)-ROW(),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Bob Davison" wrote in message
...

That is really cool, Biff.

I'm doing a study of a company merger ruling for 2 union employee groups.
Seniority is very important to them and I am currently looking at the
resultant disparity between employees from each company with simular
original employment dates.

Do you think you could figure it out to go the other way? Meaning, the
number of rows between a company A employee and the most senior company B
employee hired the same day or later?


1 B 7/27/81
2 B 9/12/82
3 A 9/23/84
4 A 2/28/87
5 B 9/22/84
6 A 3/15/89
7 B 10/3/84
8 B 6/10/85
9 B 7/12/89

A employee #3 should return 4 (4 rows away from B employee hired later
than 9/23/84)
A employee #4 should return 5 (5 rows away from B employee hired later
than 2/28/87)
A employee #6 should return 3 (3 rows away from A employee hired later
than 3/15/89)

Thank you.

Bob