Creating a Rotating Seniority List based on being asked and being accepted.

[Squeak74]

Hello,

I am trying to create a rotating list of workers based on seniority, that would rotate based on the answers to two questions.
1. Were they asked? Y or N
a. If Y, then they would move to the bottom of the list.
b. If N, then they would not move down, but could move up if the person below them were asked.
2. Did they accept? Y or N
a. If Y, then they would move to the bottom of the list.
b. If N, then they would still move to the bottom of the list.

 

[Peter_SSs]

Welcome to the MrExcel board!

Without any sample data and expected results, I am unsure what you have and what you want, based on that description.
For one thing, you said the list would be based on the answers to two questions. However, it seems that the second question is irrelevant since the same thing happens no matter the answer to that question.

2. Did they accept? Y or N
a. If Y, then they would move to the bottom of the list.
b. If N, then they would still move to the bottom of the list.

I'm also unsure what you mean by a "rotating" list.

Please consider constructing a small set of dummy sample data and post that, along with the expected results and explain again in relation to the specific sample data.

 

[Squeak74]

Welcome to the MrExcel board!

Without any sample data and expected results, I am unsure what you have and what you want, based on that description.
For one thing, you said the list would be based on the answers to two questions. However, it seems that the second question is irrelevant since the same thing happens no matter the answer to that question.



I'm also unsure what you mean by a "rotating" list.

Please consider constructing a small set of dummy sample data and post that, along with the expected results and explain again in relation to the specific sample data.

My apologies for the confusion. Below please see my example.

Original List

EMPLOYEE NAME	SENIORITY DATE	OT OFFERED	ACCEPTED
Appleton, Alphonso	7/1/1996	Y	N
Ballard, Brian	6/1/1999	N	N
Charleston, Chuck	4/23/2001	N	N
Davis, Dean	12/3/2001	Y	Y
Eggleston, Edgar	7/3/2004	N	N
Franklin, Frank	4/17/2005	N	N
Green, George	7/14/2006	N	N
Homan, Howard	10/2/2006	N	N
New List
EMPLOYEE NAME	SENIORITY DATE	OT OFFERED	ACCEPTED
Ballard, Brian	6/1/1999	N	N
Charleston, Chuck	4/23/2001	N	N
Eggleston, Edgar	7/3/2004	N	N
Franklin, Frank	4/17/2005	N	N
Green, George	7/14/2006	N	N
Homan, Howard	10/2/2006	N	N
Appleton, Alphonso	7/1/1996	Y	N
Davis, Dean	12/3/2001	Y	Y

In the original list they are all in order based on seniority date. If they are asked, they move to the bottom, but if they aren't they would remain in line.

So, the new list is based on OT offered, then the person that accepts is at the absolute bottom of the list. Therefore, the next person to be offered OT would be Ballard, Brian based on this list, but if he is on vacation, then we would go to Charleston, Chuck.

 

[Peter_SSs]

Thanks for the sample data.

Is this what you want then?

Squeak74.xlsm
	A	B	C	D
1	EMPLOYEE NAME	SENIORITY DATE	OT OFFERED	ACCEPTED
2	Appleton, Alphonso	1/07/1996	Y	N
3	Ballard, Brian	1/06/1999	N	N
4	Charleston, Chuck	23/04/2001	N	N
5	Davis, Dean	3/12/2001	Y	Y
6	Eggleston, Edgar	3/07/2004	N	N
7	Franklin, Frank	17/04/2005	N	N
8	Green, George	14/07/2006	N	N
9	Homan, Howard	2/10/2006	N	N
10
11	New List
12	EMPLOYEE NAME	SENIORITY DATE	OT OFFERED	ACCEPTED
13	Ballard, Brian	1/06/1999	N	N
14	Charleston, Chuck	23/04/2001	N	N
15	Eggleston, Edgar	3/07/2004	N	N
16	Franklin, Frank	17/04/2005	N	N
17	Green, George	14/07/2006	N	N
18	Homan, Howard	2/10/2006	N	N
19	Appleton, Alphonso	1/07/1996	Y	N
20	Davis, Dean	3/12/2001	Y	Y
Seniority List

Cell Formulas
Range	Formula
A13:A20	A13	=INDEX(A$2:A$9,IFERROR(AGGREGATE(15,6,(ROW(A$2:A$9)-ROW(A$2)+1)/(C$2:C$9="N"),ROWS(A$13:A13)),AGGREGATE(15,6,(ROW(A$2:A$9)-ROW(A$2)+1)/(C$2:C$9="Y"),ROWS(A$13:A13)-COUNTIF(C$2:C$9,"N"))))
B13:D20	B13	=INDEX(B$2:B$9,MATCH($A13,$A$2:$A$9,0))

 

[Travismurdock]

Hello. How can I expand this list to 15 people.

 

[Peter_SSs]

Hello. How can I expand this list to 15 people.

Welcome to the MrExcel board!

Just add 7 new rows in the middle of the top section and add/adjust the data in that top section.
Then drag all the bottom section formulas down another 7 rows.

 

[BoilerMan691]

Hello Peter,

Would it be possible to have a formula that works similarly to the one you've posted above above with the exception that if the person is offered the overtime (Y in Column C), but does not accept the overtime (N in Column D) they then move to the new list below the people who were N/N but they are placed above the people who are Y/Y. I hope that makes sense, if not I can also supply sample data to better help explain my request.

Thank you!
Jim

 

[Peter_SSs]

Would it be possible to have a formula that works similarly to the one you've posted above above with the exception that if the person is offered the overtime (Y in Column C), but does not accept the overtime (N in Column D) they then move to the new list below the people who were N/N but they are placed above the people who are Y/Y.

Welcome to the MrExcel board!
Isn't that what already happens?
Example in post 4 is Appleton who is Y/N. In the results Appleton is below all the N/N and above the Y/Y

 

[BoilerMan691]

Welcome to the MrExcel board!
Isn't that what already happens?
Example in post 4 is Appleton who is Y/N. In the results Appleton is below all the N/N and above the Y/Y

You are correct, but it only happened in this scenario because Appleton was marked as Y/N before Davis was marked as Y/Y. In my case, I oftentimes have to canvas out multiple OT slots on a single day/shift and so using the formula you've given, if I move BEYOND any of the people who have are marked as Y/Y and use a Y/N before getting to another Y/Y then those marked as Y/N will land in between those marked as Y/Y. Essentially, I'm looking for a formula close to the one you've posted here, except I need it to rotate names in the order of N/N, Y/N, Y/Y with those being marked as N/N being rotated to the top and those being marked as Y/Y being rotated to the bottom, while those marked as Y/N land in between. Please let me know if I need to clarify anything further, and I truly appreciate any time and effort you put into this one.

 

[Peter_SSs]

but it only happened in this scenario because Appleton was marked as Y/N before Davis was marked as Y/Y.

OK. Does this work better?

24 03 31.xlsm
	A	B	C	D
1	EMPLOYEE NAME	SENIORITY DATE	OT OFFERED	ACCEPTED
2	Appleton, Alphonso	1/07/1996	Y	Y
3	Ballard, Brian	1/06/1999	N	N
4	Charleston, Chuck	23/04/2001	N	N
5	Davis, Dean	3/12/2001	Y	N
6	Eggleston, Edgar	3/07/2004	N	N
7	Franklin, Frank	17/04/2005	N	N
8	Green, George	14/07/2006	N	N
9	Homan, Howard	2/10/2006	N	N
10
11	New List
12	EMPLOYEE NAME	SENIORITY DATE	OT OFFERED	ACCEPTED
13	Ballard, Brian	1/06/1999	N	N
14	Charleston, Chuck	23/04/2001	N	N
15	Eggleston, Edgar	3/07/2004	N	N
16	Franklin, Frank	17/04/2005	N	N
17	Green, George	14/07/2006	N	N
18	Homan, Howard	2/10/2006	N	N
19	Davis, Dean	3/12/2001	Y	N
20	Appleton, Alphonso	1/07/1996	Y	Y
Seniority List

Cell Formulas
Range	Formula
A13:D20	A13	=SORTBY(A2:D9,C2:C9,1,D2:D9,1)
Dynamic array formulas.