Picking Times from a List

[Steve 1962]

Hi

I have a list of events (column A) and associated dates & times (columns B & C). I wish to grab the "commence" and "end" times for a particular event and place them in a summary area of an adjacent worksheet. As can be seen by my example, the dates & times that I have manually extracted can be found in cells F14:G15. How do I extract these values automatically with formulas ?

Thanks

Steve

Excel Workbook
	A	B	C	D	E	F	G
1		Commence	End
2	Event 1	12:17 3-May	18:40 3-May
3	Event 1	17:15 3-May	21:00 3-May
4	Event 1	16:30 4-May	20:10 4-May
5	Event 1	19:00 4-May	01:43 5-May
6	Event 1	20:55 4-May	03:38 5-May
7	Event 2	04:00 5-May	04:50 5-May
8	Event 2	06:00 5-May	06:45 5-May
9	Event 2	08:00 5-May	08:15 5-May
10	Event 2	09:37 5-May	10:26 5-May
11	Event 2	11:00 5-May	12:45 5-May
12	Event 2	14:31 5-May	15:36 5-May
13						Commence	End
14					Event 1	12:17 3-May	03:38 5-May
15					Event 2	04:00 5-May	15:36 5-May
Sheet1

Excel 2007

 

[Jon von der Heyden]

Is it always the 1st occurrence you want to return?

Commence:
=VLOOKUP(E14,A:B,2,0)

End:
=VLOOKUP(E14,A:C,3,0)

 

[grizle]

Use a pivot table looking for min of Commence and max of End against Events

 

[Jon von der Heyden]

Ok so I just understood the question. You want the earliest commence date and the latest end date.

Given that your table is sorted...

Commence:
=VLOOKUP(E14,A:B,2,FALSE)

End:
=VLOOKUP(E14,A:C,3,TRUE)

 

[Steve 1962]

Thanks for your reply Jon.

Sorry if I'm not explaining this well - I'll try again. The COMMENCE is working OK however, the END is what is giving me trouble. I didn't mention that the list is a continuous list which extends beyond row 12 (eg: there is event 3, then there is event 4, and so on ....). I need the END date associated with that particular event. The END formula with the TRUE at the end of it, is giving me the last entry at the bottom of the list in row 156 (in my case, the time and date on the extended list is a value on the 28 May which is at the bottom of my list).

Hope this helps.

Steve

 

[grizle]

So whats wrong with a pivot table? Other than the formating is a little different. It will pick up all events from the table and return the earliest start and latest finish times.

Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center">Event</TD><TD style="TEXT-ALIGN: center">Commence</TD><TD style="TEXT-ALIGN: center">End</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: right">03/05/2011 12:17</TD><TD style="TEXT-ALIGN: right">03/05/2011 18:40</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: right">03/05/2011 17:15</TD><TD style="TEXT-ALIGN: right">03/05/2011 21:00</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: right">04/05/2011 16:30</TD><TD style="TEXT-ALIGN: right">04/05/2011 20:10</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: right">04/05/2011 19:00</TD><TD style="TEXT-ALIGN: right">05/05/2011 01:43</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: right">04/05/2011 20:55</TD><TD style="TEXT-ALIGN: right">05/05/2011 03:38</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: right">05/05/2011 04:00</TD><TD style="TEXT-ALIGN: right">05/05/2011 04:50</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: right">05/05/2011 06:00</TD><TD style="TEXT-ALIGN: right">05/05/2011 06:45</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: right">05/05/2011 08:00</TD><TD style="TEXT-ALIGN: right">05/05/2011 08:15</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: right">05/05/2011 09:37</TD><TD style="TEXT-ALIGN: right">05/05/2011 10:26</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: right">05/05/2011 11:00</TD><TD style="TEXT-ALIGN: right">05/05/2011 12:45</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: right">05/05/2011 14:31</TD><TD style="TEXT-ALIGN: right">05/05/2011 15:36</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Event</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Data</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Total</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Event 1</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Min of Commence</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">03/05/2011 12:17</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Max of End</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">05/05/2011 03:38</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Event 2</TD><TD style="BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">Min of Commence</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/05/2011 04:00</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">Max of End</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">05/05/2011 15:36</TD></TR></TBODY></TABLE>

Sheet1​

 

[Steve 1962]

Given the structure of this workbook (multiple interconnected workhsheets), a Pivot Table is unsuitable.

 

[Jon von der Heyden]

Provided your list is ordered by Event name then what I supplied should be fine.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Commence</td><td style=";">End</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Event 1</td><td style="text-align: right;;">03/05/2011 12:17</td><td style="text-align: right;;">03/05/2011 18:40</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Event 1</td><td style="text-align: right;;">03/05/2011 17:15</td><td style="text-align: right;;">03/05/2011 21:00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Event 1</td><td style="text-align: right;;">04/05/2011 16:30</td><td style="text-align: right;;">04/05/2011 20:10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Event 1</td><td style="text-align: right;;">04/05/2011 19:00</td><td style="text-align: right;;">05/05/2011 01:43</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Event 1</td><td style="text-align: right;;">04/05/2011 20:55</td><td style="text-align: right;;">05/05/2011 03:38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Event 2</td><td style="text-align: right;;">05/05/2011 04:00</td><td style="text-align: right;;">05/05/2011 04:50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Event 2</td><td style="text-align: right;;">05/05/2011 06:00</td><td style="text-align: right;;">05/05/2011 06:45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Event 2</td><td style="text-align: right;;">05/05/2011 08:00</td><td style="text-align: right;;">05/05/2011 08:15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Event 2</td><td style="text-align: right;;">05/05/2011 09:37</td><td style="text-align: right;;">05/05/2011 10:26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Event 2</td><td style="text-align: right;;">05/05/2011 11:00</td><td style="text-align: right;;">05/05/2011 12:45</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Event 2</td><td style="text-align: right;;">05/05/2011 14:31</td><td style="text-align: right;;">05/05/2011 15:36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Commence</td><td style=";">End</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Event 1</td><td style="text-align: right;;">03/05/2011 12:17</td><td style="text-align: right;;">05/05/2011 03:38</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Event 2</td><td style="text-align: right;;">05/05/2011 04:00</td><td style="text-align: right;;">05/05/2011 15:36</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">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: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><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: #DAE7F5;color: #161120">F14</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E14,A:B,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G14</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E14,A:C,3,TRUE</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Trevor G]

I would concur with Jon this works well.

Would be interesting to find out the steps for the Pivot Table option as I am trying different ways to come up with a solution for this.

 

[Steve 1962]

Jon

Does it matter that I have included a named list into the VLOOKUP formula instead of a reference to columns (eg: "A" and "B" as you have noted). The summary that I am using is on a different worksheet.

Still having trouble.

S