Auto Assign 20 Vans to 50 employees using Excel Vlookup

[Katrix1982]

Hi,
I have to assign vans to my employees every morning. Each employee has a preference of which Van they want to use. Currently I am doing this by hand and comparing a sheet with employees and preferred van and trying to assign them to their van. I have 50 employees but 20 vans, so all 50 employees have a van preference from 1 to 20, repeated as needed. If it was 20 vans and 20 employees I would just use Vlookup to assign the vans. With 50 employees and 2 employees with same preference working the same day I am not sure how I can accomplish this.

I have a 3 sheets, one sheet (Vans) has van numbers 1 to 20. Second sheet has (Employees 50-75 with a preferred van number). I want to know if there is a way I can make it so that if an employee name is added to a (Blank) third sheet it would do a Vlookup to see which van the employee prefers, but if the Van is already assigned to another employee, it would pick another unassigned van for the employee.

I can get van preferences from employees where they pick 1st 2nd 3rd 4th 5th preferred van if needed.

Is there a way to do this in Excel?

Thanks.

 

[Dermot]

This is very difficult to do in Excel, and even in VBA it is a little tricky. It also requires more information on how you would decide between competing preferences.

If I were doing it, I would probably run down the list of employees, allocating vans where there is no competition, then go back through those where multiple employees want the same van, choosing randomly which one wins, then look at the further preferences of the losers. If one of them ran out of preferences, they would have to be allocated randomly.

I think you should pay someone to write code to do this. I expect it would take a few hours work (but not days).

 

[Toadstool]

Hi Katrix1982,

Allocation of resources using Excel gets tricky and you usually need to become somewhat convoluted to avoid circular references. Here is my convoluted offering.

Here's your Blank sheet where you enter the names. I'm assuming that's where you designate who is on shift that particular day (NOTE: Allocations are made in the sequence entered so if two people prefer the same van then the first name on this sheet will be allocated the van).

Katrix1982.xlsx
	A	B	C
1	On Shift		Van
2	Emily		Van12
3	Ethan		Van09
4	Elizabeth		Van03
5	Jacob		Van01
6	Michael		Van02
7	Mila		Van04
8	Daniel		Van14
9	Ella		Van05
10	Avery		Van06
11	Henry		Van07
12	Jackson		Van08
13	Sofia		Van10
14
Blank

Cell Formulas
Range	Formula
C2:C14	C2	=INDEX(Vans!$T$2:$T$100,MATCH(A2,Vans!$M$2:$M$101,0))

Here's your Employees sheet with the preferences.

Katrix1982.xlsx
	A	B	C	D	E	F	G
1	Employee	Pref1	Pref2	Pref3	Pref4	Pref5	Working Today
2	Alexander
3	Emily	Van12	Van03	Van19			Y
4	Ethan	Van09	Van12				Y
5	Elizabeth	Van12	Van03	Van09	Van13		Y
6	Jacob	Van12	Van03				Y
7	Michael						Y
8	Mila						Y
9	Daniel	Van14					Y
10	Ella	Van12					Y
11	Avery						Y
12	Henry						Y
13	Jackson						Y
14	Sofia						Y
15	Camila
16	Sebastian
Employees

Cell Formulas
Range	Formula
G2:G16	G2	=IF(ISNA(MATCH(A2,Vans!$M$2:$M$100,0)),"","Y")

Here's the Vans sheet with the list of vans and who gets allocated to each. I'm using columns K to T as a work area to figure out the allocations but this could be cut & pasted to a hidden sheet if you want.

Katrix1982.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Van		Allocated								Preferred		Shift
2	Van01		Jacob										Emily	Van12	Van12	Van12	Van12	Van12	0	Van12
3	Van02		Michael										Ethan	Van09	Van09	Van09	Van09	Van09	0	Van09
4	Van03		Elizabeth								Elizabeth		Elizabeth	Van12	Van03	Van03	Van03	Van03	0	Van03
5	Van04		Mila										Jacob	Van12	Van03	0	0	0	1	Van01
6	Van05		Ella										Michael	0	0	0	0	0	2	Van02
7	Van06		Avery										Mila	0	0	0	0	0	3	Van04
8	Van07		Henry										Daniel	Van14	Van14	Van14	Van14	Van14	3	Van14
9	Van08		Jackson										Ella	Van12	0	0	0	0	4	Van05
10	Van09		Ethan								Ethan		Avery	0	0	0	0	0	5	Van06
11	Van10		Sofia										Henry	0	0	0	0	0	6	Van07
12	Van11												Jackson	0	0	0	0	0	7	Van08
13	Van12		Emily								Emily		Sofia	0	0	0	0	0	8	Van10
14	Van13												0						8
15	Van14		Daniel								Daniel		0						8
16	Van15												0						8
17	Van16												0						8
Vans

Cell Formulas
Range	Formula
M2:M17	M2	=INDEX(Blank!$A$2:$A$101,ROW()-1)
N2:N17	N2	=IF(M2=0,"",INDEX(Employees!$B$2:$B$100,MATCH(M2,Employees!$A$2:$A$100,0)))
O2:R17	O2	=IF($M2=0,"",IF(COUNTIF($N$1:N1,N2)>0,INDEX(Employees!C$2:C$100,MATCH($M2,Employees!$A$2:$A$100,0)),N2))
S2:S17	S2	=COUNTIF($R$1:R2,0)
T2:T17	T2	=IF(R2<>0,R2,INDEX($A$2:$A$21,AGGREGATE(15,6,ROW($A$2:$A$21)-ROW($A$1)/($K$2:$K$21=""),S2)))
C2:C17	C2	=IFERROR(INDEX($M$2:$M$100,MATCH(A2,$T$2:$T$100,0)),"")
K2:K17	K2	=IF(A2="","",IFERROR(INDEX($M$2:$M$100,MATCH(A2,$R$2:$R$100,0)),""))

I thought about randomizing the allocation so it's a litle more fair than relying on the sequence of names entered but the Excel RANDOM functions recalculate when any changes are made so I thought that might look confusing. Maybe a priority could be calculated based on the name and the date so it changes each day?

 

[alansidman]

crossposted: assigning 20 vans to 50 employees based on preference

 

[Fluff]

@Katrix1982
While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Toadstool]

Hi Katrix1982,

I've added some more convoluted logic to quasi randomize the Employee priority of getting their preferred van (when preferred vans are duplicated across employees) based on the first and last letters of employee name and date.

This means when your PC clocks over midnight the priorities will change for 24 hours, so giving a more fair allocation. Let me know if you want me to post that solution.