Hello,

I have an employee seniority list with over 5000 rows that is the result of
2 companies joining into one, company A and company B. The list was not put
together based on length of service at each company, but by another
formula - resulting in the fact that the employee hire dates are not in
chronological order.

Company A employees were inserted into the list ahead of Company B employees
with more years of service.

I want to figure out for each employee from company B how many rows above is
the lowest senoirity employee from Company A with the same or earlier
employee hire date.

Within the data I have a column with the previous company code, A or B, and
a column of original employment dates.

Thanks

The RANK function will give you the relative position if you do it based on
HIRE DATE.

"Bob Davison" wrote:

Hello,

I have an employee seniority list with over 5000 rows that is the result of
2 companies joining into one, company A and company B. The list was not put
together based on length of service at each company, but by another
formula - resulting in the fact that the employee hire dates are not in
chronological order.

Company A employees were inserted into the list ahead of Company B employees
with more years of service.

I want to figure out for each employee from company B how many rows above is
the lowest senoirity employee from Company A with the same or earlier
employee hire date.

Within the data I have a column with the previous company code, A or B, and
a column of original employment dates.

Thanks

Thank you for responding. I am not sure what you mean.

Here is an example of my data:

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

B employee #8 should return 5 (5 rows away from A employee hired ealier than
7/12/88)
B employee #7 should return 5 (5 rows away from A employee hired ealier than
6/10/85)
B employee #6 should return 4 (4 rows away from A employee hired ealier than
10/3/84)
B employee #4 should return 3 (3 rows away from A employee hired ealier than
10/3/84)

Thank you for helping!

Bob


"Barb Reinhardt" wrote in message
...
The RANK function will give you the relative position if you do it based
on
HIRE DATE.

Try this ("lightly" tested!)

Here is an example of my data:

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

Assume that data is in the range A1:C8

Enter this formula in D1 and copy down as needed:

=IF(B1="B",ROW()-MAX(INDEX((B$1:B$8="A")*(C$1:C$8<=C1)*ROW(C$1:C$8) ,,1)),"")

If you have 5000 rows of data this may be a little slow.

On a side note: What does this result mean?

Biff

"Bob Davison" wrote in message
...
Thank you for responding. I am not sure what you mean.

Here is an example of my data:

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

B employee #8 should return 5 (5 rows away from A employee hired ealier
than 7/12/88)
B employee #7 should return 5 (5 rows away from A employee hired ealier
than 6/10/85)
B employee #6 should return 4 (4 rows away from A employee hired ealier
than 10/3/84)
B employee #4 should return 3 (3 rows away from A employee hired ealier
than 10/3/84)

Thank you for helping!

Bob


"Barb Reinhardt" wrote in
message ...
The RANK function will give you the relative position if you do it based
on
HIRE DATE.

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

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