Equally split column list to a section of people

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
300
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>
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
Hi, something like this maybe?

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Veh</td><td style=";">Allocated</td><td style="text-align: right;;"></td><td style=";">Names</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">van</td><td style="background-color: #FFFF00;;">Dave</td><td style="text-align: right;;"></td><td style=";">Dave</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">van</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style=";">Jim</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">van</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style=";">Steve</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">van</td><td style=";">Bob</td><td style="text-align: right;;"></td><td style=";">Bob</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">van</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">van</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Car</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Car</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Car</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Car</td><td style=";">Bob</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Car</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Car</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Car</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Car</td><td style=";">Bob</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Car</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Car</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Car</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">bus</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">bus</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">bike</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">bike</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">bike</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">bike</td><td style=";">Bob</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">bike</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">bike</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">bike</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">bike</td><td style=";">Bob</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">bike</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=INDEX(<font color="Blue">$D$2:$D$5,1+MOD(<font color="Red">COUNTIFS(<font color="Green">$A$2:$A2,A2</font>)-1,COUNTA(<font color="Green">$D$2:$D$5</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
300
This is amazing FormR thank you so much. Is there an easy way for me to understand how the formula works
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Since you cannot split bikes and cars in half in this world :) I guess maybe you want this? If I got it correctly:


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Dave</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet10</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">A$2:A2,A2</font>)<=INT(<font color="Red">COUNTIF(<font color="Green">$A$2:$A$29,A2</font>)/COUNTA(<font color="Green">E:E</font>)</font>)*COUNTA(<font color="Red">E:E</font>),INDEX(<font color="Red">E:E,MOD(<font color="Green">COUNTIF(<font color="Purple">A$2:A2,A2</font>)-1,COUNTA(<font color="Purple">E:E</font>)</font>)+1</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

and drag down


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Veh</td><td style="font-weight: bold;;">Allocated</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dave</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">van</td><td style="text-align: right;;">1</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style=";">Jim</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">van</td><td style="text-align: right;;">2</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style=";">Steve</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">van</td><td style="text-align: right;;">3</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style=";">Bobby</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">van</td><td style="text-align: right;;">4</td><td style=";">Bobby</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">van</td><td style="text-align: right;;">1</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">van</td><td style="text-align: right;;">2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Car</td><td style="text-align: right;;">1</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Car</td><td style="text-align: right;;">2</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Car</td><td style="text-align: right;;">3</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Car</td><td style="text-align: right;;">4</td><td style=";">Bobby</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Car</td><td style="text-align: right;;">1</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Car</td><td style="text-align: right;;">2</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Car</td><td style="text-align: right;;">3</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Car</td><td style="text-align: right;;">4</td><td style=";">Bobby</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Car</td><td style="text-align: right;;">1</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Car</td><td style="text-align: right;;">2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Car</td><td style="text-align: right;;">3</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">bus</td><td style="text-align: right;;">1</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">bus</td><td style="text-align: right;;">2</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">bike</td><td style="text-align: right;;">1</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">bike</td><td style="text-align: right;;">2</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">bike</td><td style="text-align: right;;">3</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">bike</td><td style="text-align: right;;">4</td><td style=";">Bobby</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">bike</td><td style="text-align: right;;">1</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">bike</td><td style="text-align: right;;">2</td><td style=";">Jim</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">bike</td><td style="text-align: right;;">3</td><td style=";">Steve</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">bike</td><td style="text-align: right;;">4</td><td style=";">Bobby</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">bike</td><td style="text-align: right;;">1</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet10</p><br /><br />
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
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

Active Member
Joined
Aug 16, 2011
Messages
300
This is amazing also VBA Geek. I can use both of these solutions thank you so much guys.
 

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
300
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

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
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

Active Member
Joined
Aug 16, 2011
Messages
300
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:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,219
Office Version
365
Platform
Windows
So if there is a blank cell use the name below so ignore blank cells?
Hi, you could try like this..

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Veh</td><td style=";">Allocated</td><td style="text-align: right;;"></td><td style=";">Names</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">van</td><td style="background-color: #FFFF00;;">Angela</td><td style="text-align: right;;"></td><td style=";">Angela</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">van</td><td style=";">Bethan M</td><td style="text-align: right;;"></td><td style=";">Bethan M</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">van</td><td style=";">Chloe W</td><td style="text-align: right;;"></td><td style=";">Chloe W</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">van</td><td style=";">Debi C</td><td style="text-align: right;;"></td><td style=";">Debi C</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">van</td><td style=";">Dominika H</td><td style="text-align: right;;"></td><td style=";">Dominika H</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">van</td><td style=";">Lewis W</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">van</td><td style=";">Lorna Y</td><td style="text-align: right;;"></td><td style=";">Lewis W</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">van</td><td style=";">Louise B</td><td style="text-align: right;;"></td><td style=";">Lorna Y</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">van</td><td style=";">Luke S</td><td style="text-align: right;;"></td><td style=";">Louise B</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">van</td><td style=";">Payam N</td><td style="text-align: right;;"></td><td style=";">Luke S</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">van</td><td style=";">Sonia P</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">van</td><td style=";">Danielle B</td><td style="text-align: right;;"></td><td style=";">Payam N</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">car</td><td style=";">Angela</td><td style="text-align: right;;"></td><td style=";">Sonia P</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Car</td><td style=";">Bethan M</td><td style="text-align: right;;"></td><td style=";">Danielle B</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Car</td><td style=";">Chloe W</td><td style="text-align: right;;"></td><td style=";">Spare 1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Car</td><td style=";">Debi C</td><td style="text-align: right;;"></td><td style=";">Spare 2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Car</td><td style=";">Dominika H</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">bus</td><td style=";">Angela</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">bus</td><td style=";">Bethan M</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">bike</td><td style=";">Angela</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">bike</td><td style=";">Bethan M</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">bike</td><td style=";">Chloe W</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">bike</td><td style=";">Debi C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">bike</td><td style=";">Dominika H</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">bike</td><td style=";">Lewis W</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">bike</td><td style=";">Lorna Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">bike</td><td style=";">Louise B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">bike</td><td style=";">Luke S</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=INDEX(<font color="Blue">$D$2:$D$19,AGGREGATE(<font color="Red">15,6,(<font color="Green">ROW(<font color="Purple">$D$2:$D$19</font>)-ROW(<font color="Purple">$D$2</font>)+1</font>)/(<font color="Green">LEN(<font color="Purple">$D$2:$D$19</font>)>0</font>),1+MOD(<font color="Green">COUNTIFS(<font color="Purple">$A$2:$A2,A2</font>)-1,COUNTA(<font color="Purple">$D$2:$D$19</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,082,323
Messages
5,364,590
Members
400,808
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top