Equally split column list to a section of people

[thedeadzeds]

Guys, im pretty sure this is not possible but wanted to check. I have a list of vehicles in column A and a list of 4 names in D1:D4 (dave, bob, jim and steve). I was wondering if it is possible to allocate the vehicles equally to the people named in D1:D4 so each person has an equal amount of vans, cars, bus and bikes. The results would be shown in column B. I do realise that it cannot always be an equally split but as close to as possible would be ok. Hope this makes sense. Thanks

Dave

Jim

Steve

Bob

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<tbody>
</tbody>

Veh	Allocated
van
van
van
van
van
van
Car
Car
Car
Car
Car
Car
Car
Car
Car
Car
Car
bus
bus
bike
bike
bike
bike
bike
bike
bike
bike
bike

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[thedeadzeds]

Thanks for this, the test works fine. However, when i apply it to my real data it doesn't seem to like it. I have changed the formula to

=INDEX(Allocate!$F$2:$F$20,AGGREGATE(15,6,(ROW(Allocate!$F$2:$F$20)-ROW(Allocate!$F$2)+1)/(LEN(Allocate!$F$2:$F$20)>0),1+MOD(COUNTIFS($L$2:$L2,L2)-1,COUNTA(Allocate!$F$2:$F$20))))

Column F also has the formula

=IF(E3<>0, D3, "")

The results are showing as per table results below with #NUM! errors. Sorry about this


Names table in the 'Allocate' Tab

	D	E	F
1	Name 1	Count	Name 2
2	Angela R	50	Angela R
3	Bethan M	50	Bethan M
4	Chloe W
5	Debi C
6	Dominika H
7	Donna W
8	Joanne S
9	Lewis W
10	Lorna Y
11	Louise B
12	Luke
13	Nicholas L	50	Nicholas L
14	Nicole F	50	Nicole F
15	Nicole M	50	Nicole M
16	Payam N	50	Payam N
17	Sonia P	50	Sonia P
18	Danielle B	50	Danielle B
19	Spare 1	50	Spare 1
20	Spare 2	50	Spare 2

<tbody>
</tbody>

Results

Angela R

Angela R

Bethan M

Angela R

Nicholas L

Nicole F

Nicole M

Angela R

Bethan M

Nicholas L

Nicole F

Payam N

Angela R

Sonia P

Bethan M

Bethan M

Danielle B

Nicholas L

Spare 1

Spare 2

#NUM!

#NUM!

#NUM!

#NUM!

Nicole F

Nicole M

<tbody>
</tbody>

 

[FormR]

Column F also has the formula

=IF(E3<>0, D3, "")

Hi, try changing this part of the formula:

COUNTA(Allocate!$F$2:$F$20)

to...

COUNTIFS(Allocate!$F$2:$F$20,"?*")

 

[thedeadzeds]

Unbelievable, works like a treat. Can't thank you enough its very much appreciated.
Regards