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

[TABLE="class: grid, width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Dave[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 153"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Veh[/TD]
[TD]Allocated[/TD]
[/TR]
[TR]
[TD]van[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]van[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]van[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]van[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]van[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]van[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Car[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bus[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bus[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]bike[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="class: grid, width: 622"]
<tbody>[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Name 1[/TD]
[TD]Count[/TD]
[TD]Name 2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Angela R[/TD]
[TD]50[/TD]
[TD]Angela R[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Bethan M[/TD]
[TD]50[/TD]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Chloe W[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Debi C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Dominika H[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Donna W[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Joanne S[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Lewis W[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Lorna Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Louise B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Luke[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Nicholas L[/TD]
[TD]50[/TD]
[TD]Nicholas L[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Nicole F[/TD]
[TD]50[/TD]
[TD]Nicole F[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Nicole M[/TD]
[TD]50[/TD]
[TD]Nicole M[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD]Payam N[/TD]
[TD]50[/TD]
[TD]Payam N[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD]Sonia P[/TD]
[TD]50[/TD]
[TD]Sonia P[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD]Danielle B[/TD]
[TD]50[/TD]
[TD]Danielle B[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD]Spare 1[/TD]
[TD]50[/TD]
[TD]Spare 1[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD]Spare 2[/TD]
[TD]50[/TD]
[TD]Spare 2[/TD]
[/TR]
</tbody>[/TABLE]


Results

[TABLE="class: grid, width: 140"]
<tbody>[TR]
[TD]Angela R[/TD]
[/TR]
[TR]
[TD]Angela R[/TD]
[/TR]
[TR]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD]Angela R[/TD]
[/TR]
[TR]
[TD]Nicholas L[/TD]
[/TR]
[TR]
[TD]Nicole F[/TD]
[/TR]
[TR]
[TD]Nicole M[/TD]
[/TR]
[TR]
[TD]Angela R[/TD]
[/TR]
[TR]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD]Nicholas L[/TD]
[/TR]
[TR]
[TD]Nicole F[/TD]
[/TR]
[TR]
[TD]Payam N[/TD]
[/TR]
[TR]
[TD]Angela R[/TD]
[/TR]
[TR]
[TD]Sonia P[/TD]
[/TR]
[TR]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD]Danielle B[/TD]
[/TR]
[TR]
[TD]Nicholas L[/TD]
[/TR]
[TR]
[TD]Spare 1[/TD]
[/TR]
[TR]
[TD]Spare 2[/TD]
[/TR]
[TR]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]#NUM![/TD]
[/TR]
[TR]
[TD]Nicole F[/TD]
[/TR]
[TR]
[TD]Nicole M[/TD]
[/TR]
</tbody>[/TABLE]

 

[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