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]

 

[FormR]

Hi, something like this maybe?

Excel 2013/2016
	A	B	C	D
1	Veh	Allocated		Names
2	van	Dave		Dave
3	van	Jim		Jim
4	van	Steve		Steve
5	van	Bob		Bob
6	van	Dave
7	van	Jim
8	Car	Dave
9	Car	Jim
10	Car	Steve
11	Car	Bob
12	Car	Dave
13	Car	Jim
14	Car	Steve
15	Car	Bob
16	Car	Dave
17	Car	Jim
18	Car	Steve
19	bus	Dave
20	bus	Jim
21	bike	Dave
22	bike	Jim
23	bike	Steve
24	bike	Bob
25	bike	Dave
26	bike	Jim
27	bike	Steve
28	bike	Bob
29	bike	Dave
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX($D$2:$D$5,1+MOD(COUNTIFS($A$2:$A2,A2)-1,COUNTA($D$2:$D$5)))

 

[thedeadzeds]

This is amazing FormR thank you so much. Is there an easy way for me to understand how the formula works

 

[VBA Geek]

Since you cannot split bikes and cars in half in this world I guess maybe you want this? If I got it correctly:

Excel 2010
	C
2	Dave
Sheet10

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(A$2:A2,A2)<=INT(COUNTIF($A$2:$A$29,A2)/COUNTA(E:E))*COUNTA(E:E),INDEX(E:E,MOD(COUNTIF(A$2:A2,A2)-1,COUNTA(E:E))+1),"")

and drag down

Excel 2010
	A	B	C	D	E
1	Veh	Allocated			Dave
2	van	1	Dave		Jim
3	van	2	Jim		Steve
4	van	3	Steve		Bobby
5	van	4	Bobby
6	van	1
7	van	2
8	Car	1	Dave
9	Car	2	Jim
10	Car	3	Steve
11	Car	4	Bobby
12	Car	1	Dave
13	Car	2	Jim
14	Car	3	Steve
15	Car	4	Bobby
16	Car	1
17	Car	2
18	Car	3
19	bus	1
20	bus	2
21	bike	1	Dave
22	bike	2	Jim
23	bike	3	Steve
24	bike	4	Bobby
25	bike	1	Dave
26	bike	2	Jim
27	bike	3	Steve
28	bike	4	Bobby
29	bike	1
Sheet10

 

[FormR]

Is there an easy way for me to understand how the formula works

Try to concentrate on this part:

1+MOD(COUNTIFS($A$2:$A2,A2)-1,COUNTA($D$2:$D$5))

Which essentially creates a repeating sequence of numbers 1-4 which reset after each change of "Veh"

Using the "Evaluate Formula" tool on the formulas tab and splitting the various functions out into separate cells often helps to break it down for me.

 

[thedeadzeds]

This is amazing also VBA Geek. I can use both of these solutions thank you so much guys.

 

[thedeadzeds]

Thank you again for this, I'm probably grasping at straws here but is there a way to use VBA Geeks formula to do the following:
If the name in column E is equal to Craig, only allocate 80% of what everyone else is allocated and if the name is Ange, then only allocate 60% of what everyone else is allocated, else show no name. A long shot i'm guessing????

 

[FormR]

If the name in column E is equal to Craig, only allocate 80% of what everyone else is allocated and if the name is Ange, then only allocate 60% of what everyone else is allocated, else show no name.

I think that might be pushing it purely using native functions.

 

[thedeadzeds]

You kindly helped me with this formula a while back which works great. At the moment the part of the formula D2:D19 looks at the below column (column d). However, sometimes there may be gaps within the column data so the resulting formula pulls through 0's as per Result table. Is there a way to not include the blank cells in column D? So if there is a blank cell use the name below so ignore blank cells? Hope this makes sense.

[COLOR=#574123][I]=INDEX([/I][/COLOR][COLOR=Blue][I]$D$2:$D$19,1+MOD([COLOR=Red]COUNTIFS([COLOR=Green]$A$2:$A2,A2[/COLOR])-1,COUNTA([COLOR=Green]$D$2:$D$19[/COLOR])[/COLOR])[/I][/COLOR][COLOR=#574123][I])[/I][/COLOR]

COLUMN D
[TABLE="class: grid, width: 146"]
<tbody>[TR]
[TD]Angela[/TD]
[/TR]
[TR]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD]Chloe W[/TD]
[/TR]
[TR]
[TD]Debi C[/TD]
[/TR]
[TR]
[TD]Dominika H[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Lewis W[/TD]
[/TR]
[TR]
[TD]Lorna Y[/TD]
[/TR]
[TR]
[TD]Louise B[/TD]
[/TR]
[TR]
[TD]Luke S[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Payam N[/TD]
[/TR]
[TR]
[TD]Sonia P[/TD]
[/TR]
[TR]
[TD]Danielle B[/TD]
[/TR]
[TR]
[TD]Spare 1[/TD]
[/TR]
[TR]
[TD]Spare 2[/TD]
[/TR]
</tbody>[/TABLE]


RESULT TABLE
[TABLE="class: grid, width: 140"]
<tbody>[TR]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD]Chloe W[/TD]
[/TR]
[TR]
[TD]Debi C[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]Angela R[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]Bethan M[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/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]
</tbody>[/TABLE]

 

[FormR]

So if there is a blank cell use the name below so ignore blank cells?

Hi, you could try like this..

Excel 2013/2016
	A	B	C	D
1	Veh	Allocated		Names
2	van	Angela		Angela
3	van	Bethan M		Bethan M
4	van	Chloe W		Chloe W
5	van	Debi C		Debi C
6	van	Dominika H		Dominika H
7	van	Lewis W
8	van	Lorna Y		Lewis W
9	van	Louise B		Lorna Y
10	van	Luke S		Louise B
11	van	Payam N		Luke S
12	van	Sonia P
13	van	Danielle B		Payam N
14	car	Angela		Sonia P
15	Car	Bethan M		Danielle B
16	Car	Chloe W		Spare 1
17	Car	Debi C		Spare 2
18	Car	Dominika H
19	bus	Angela
20	bus	Bethan M
21	bike	Angela
22	bike	Bethan M
23	bike	Chloe W
24	bike	Debi C
25	bike	Dominika H
26	bike	Lewis W
27	bike	Lorna Y
28	bike	Louise B
29	bike	Luke S
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX($D$2:$D$19,AGGREGATE(15,6,(ROW($D$2:$D$19)-ROW($D$2)+1)/(LEN($D$2:$D$19)>0),1+MOD(COUNTIFS($A$2:$A2,A2)-1,COUNTA($D$2:$D$19))))