Equally split column list to a section of people

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
442
Office Version
  1. 365
Platform
  1. Windows
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>

VehAllocated
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>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi, something like this maybe?


Excel 2013/2016
ABCD
1VehAllocatedNames
2vanDaveDave
3vanJimJim
4vanSteveSteve
5vanBobBob
6vanDave
7vanJim
8CarDave
9CarJim
10CarSteve
11CarBob
12CarDave
13CarJim
14CarSteve
15CarBob
16CarDave
17CarJim
18CarSteve
19busDave
20busJim
21bikeDave
22bikeJim
23bikeSteve
24bikeBob
25bikeDave
26bikeJim
27bikeSteve
28bikeBob
29bikeDave
Sheet1
Cell Formulas
RangeFormula
B2=INDEX($D$2:$D$5,1+MOD(COUNTIFS($A$2:$A2,A2)-1,COUNTA($D$2:$D$5)))
 
Upvote 0
This is amazing FormR thank you so much. Is there an easy way for me to understand how the formula works
 
Upvote 0
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
2Dave
Sheet10
Cell Formulas
RangeFormula
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
ABCDE
1VehAllocatedDave
2van1DaveJim
3van2JimSteve
4van3SteveBobby
5van4Bobby
6van1
7van2
8Car1Dave
9Car2Jim
10Car3Steve
11Car4Bobby
12Car1Dave
13Car2Jim
14Car3Steve
15Car4Bobby
16Car1
17Car2
18Car3
19bus1
20bus2
21bike1Dave
22bike2Jim
23bike3Steve
24bike4Bobby
25bike1Dave
26bike2Jim
27bike3Steve
28bike4Bobby
29bike1
Sheet10
 
Upvote 0
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.
 
Upvote 0
This is amazing also VBA Geek. I can use both of these solutions thank you so much guys.
 
Upvote 0
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????
 
Upvote 0
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.
 
Upvote 0
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.

Code:
[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
Angela
Bethan M
Chloe W
Debi C
Dominika H
Lewis W
Lorna Y
Louise B
Luke S
Payam N
Sonia P
Danielle B
Spare 1
Spare 2

<tbody>
</tbody>


RESULT TABLE
Bethan M
Chloe W
Debi C
0
Angela R
0
0
Bethan M
0
0
Nicholas L
Nicole F
Nicole M
Angela R

<tbody>
</tbody>
 
Last edited:
Upvote 0
So if there is a blank cell use the name below so ignore blank cells?

Hi, you could try like this..


Excel 2013/2016
ABCD
1VehAllocatedNames
2vanAngelaAngela
3vanBethan MBethan M
4vanChloe WChloe W
5vanDebi CDebi C
6vanDominika HDominika H
7vanLewis W
8vanLorna YLewis W
9vanLouise BLorna Y
10vanLuke SLouise B
11vanPayam NLuke S
12vanSonia P
13vanDanielle BPayam N
14carAngelaSonia P
15CarBethan MDanielle B
16CarChloe WSpare 1
17CarDebi CSpare 2
18CarDominika H
19busAngela
20busBethan M
21bikeAngela
22bikeBethan M
23bikeChloe W
24bikeDebi C
25bikeDominika H
26bikeLewis W
27bikeLorna Y
28bikeLouise B
29bikeLuke S
Sheet1
Cell Formulas
RangeFormula
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))))
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top