Relative position of Employees

[Bob Davison]

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

 

[Barb Reinhardt]

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

 

[Bob Davison]

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

 

[T. Valko]

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]

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

 

[T. Valko]

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]

Thanks Biff,

That array formula seems to reference all of the "A" employees with only one
"B" employee, the first "B" employee encountered that is the same hire date
or later than the first "A" employee on the list. Any other ideas?

I appreciate your help very much.

Bob

 

[vezerid]

Thanks Biff,

That array formula seems to reference all of the "A" employees with only one
"B" employee, the first "B" employee encountered that is the same hire date
or later than the first "A" employee on the list. Any other ideas?

I appreciate your help very much.

Bob

Hi,

Maybe this *array* formula?

=ROW()-MATCH(MAX(IF(($B$1:$B9="A")*($C$1:$C9<C9),$C$1:C9)),IF($B
$1:$B9="A",$C$1:$C9),0)

This formula is attempts to work for employees of "B" and answer your
originally posted question. For employee 9 it returns 3, because the
latest-hired employee of "A", hired before E#9 is 3 rows above. Are
more senior members higher on the list? Is seniority based on
something else than hiredate?

If I change MAX to MIN it returns 6 for E#9 and 5 for E#8, which is
probably the difference of 1, if you count the present number or only
want the offset. In the latter case subtract 1 from the entire
formula.

HTH
Kostis Vezerides

 

[Bob Davison]

Thanks guys-

I think I figured out my second question.

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



Not an array.



Thanks for taking the time to help me.



Bob

 

[T. Valko]

I was waiting to see if you had success with Kostis' suggestion.

Thanks for the feedback!

Biff