Equally split column list to a section of people

thedeadzeds

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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

FormR

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

ADVERTISEMENT

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
361
Office Version
  1. 365
Platform
  1. Windows
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
361
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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,576
Office Version
  1. 365
Platform
  1. 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
361
Office Version
  1. 365
Platform
  1. Windows
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,576
Office Version
  1. 365
Platform
  1. 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,143,661
Messages
5,720,143
Members
422,267
Latest member
olund

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
Top