Picking Times from a List

S

Steve 1962

Active Member
Joined
Jan 3, 2006
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1CommenceEnd
2Event 112:17 3-May18:40 3-May
3Event 117:15 3-May21:00 3-May
4Event 116:30 4-May20:10 4-May
5Event 119:00 4-May01:43 5-May
6Event 120:55 4-May03:38 5-May
7Event 204:00 5-May04:50 5-May
8Event 206:00 5-May06:45 5-May
9Event 208:00 5-May08:15 5-May
10Event 209:37 5-May10:26 5-May
11Event 211:00 5-May12:45 5-May
12Event 214:31 5-May15:36 5-May
13CommenceEnd
14Event 112:17 3-May03:38 5-May
15Event 204:00 5-May15:36 5-May
Sheet1
Excel 2007
 
Sort by date Sort by votes
Maybe a MAX function associated with the VLOOKUP. This would provide the latest time and date for that particular event.

S
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I have tested created a Named Range called Events_List used the same formula on another sheet except used the named range and it works. See sample

Sheet3

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 101px"><COL style="WIDTH: 129px"><COL style="WIDTH: 134px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD>Commencing</TD><TD>End</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12:17 3-May</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03:38 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">04:00 5-May</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">15:36 5-May</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=VLOOKUP(A2,events_list,2,FALSE)</TD></TR><TR><TD>C2</TD><TD>=VLOOKUP(A2,events_list,3,TRUE)</TD></TR><TR><TD>B3</TD><TD>=VLOOKUP(A3,events_list,2,FALSE)</TD></TR><TR><TD>C3</TD><TD>=VLOOKUP(A3,events_list,3,TRUE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...

Original sheet

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 78px"><COL style="WIDTH: 98px"><COL style="WIDTH: 98px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Events</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Commence</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">End</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12:17 3-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">18:40 3-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">17:15 3-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">21:00 3-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">16:30 4-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20:10 4-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">19:00 4-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">01:43 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 1</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20:55 4-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">03:38 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">04:00 5-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">04:50 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">06:00 5-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">06:45 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">08:00 5-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">08:15 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">09:37 5-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10:26 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">11:00 5-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">12:45 5-May</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Event 2</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">14:31 5-May</TD><TD style="TEXT-ALIGN: center; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">15:36 5-May</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...

Named range refered to =Sheet2!$A:$C
 
Upvote 0
Jon / Trevor

I have checked and I'm sure I have everything as per your instructions. Is there any way that I could send you my spreadsheet so that you could assist in identifying where I've made a mistake ?

I'm obviously missing something.

Thanks

Steve
 
Upvote 0
Hi Steve

I'll PM you my email address, have a look and reply back to this thread in order to keep this in the public forum.
 
Upvote 0
Happy if you send a Private Message with an email address I can take a look for you. Will keep everything in the Forum to help others and make sure Jon and I don't clash.
 
Upvote 0
Hi Steve

So basically the problem with this method is that the data table is not sorted according to event name. They are grouped together but not actually ordered A-Z.

So alternative method is required.

Background for the other participants:
The Events table is in a separate sheet, with the event in column A (1), commence in column L (12) and end in column M (13). The events table has been named All_Raw_Data.

The summary lists event names in column B, starting from row 3. Related commence and end dates to be captured in columns C:D (respectively).

The formula's:
Array formula, must be confirmed with Ctl+Shift+Enter (if done correctly Excel will surround the formula with curly brackets {})

In C3, copied to end of range in column C:
=MIN(IF(INDEX(All_Raw_Data,0,1)=B3,INDEX(All_Raw_Data,0,12)))

In D3, copied to end of range in Column D:
=MAX(IF(INDEX(All_Raw_Data,0,1)=B3,INDEX(All_Raw_Data,0,13)))
 
Upvote 0
Jon

Absolutely perfect. Thanks very much for your help. Not having the events in order was what stumped me.

Yet again this forum has provided much needed assistance.

Take care.

Steve
 
Upvote 0
I've been asked to provide a small description of how these formulas work.

In terms of how the MIN(IF array formula works, please refer to Chip Pearsons explanation here (I can't explain it any better).

In terms of the INDEX function:

Say you have a 10 rows x 10 column table; A1:J10.

=INDEX(A1:J10,1,1) will return the result from A1 (the 1st row and 1st column of the table)

=INDEX(A1:J10,3,2) will return the result from B3 (the 3rd row and the 1st column of the table)

=INDEX(A1:J10,0,2) will refer to B1:B10 (because no row specified and the 2nd column)

=INDEX(A1:J10,2,0) will refer to A2:J2 (row 2 but no column specified)

=INDEX(A1:J10,0,0) will refer to A1:J10 (because no rows or columns have been specified).

Hope this clarifies...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
400
dshtesy
D
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
606
awoohaw
awoohaw
D
  • Question Question
EXTRACTING MIN AND MAX TIME FROM TIME STRING
2
Replies
10
Views
892
Fluff
Fluff
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
470
Sundance_Kid
S
A
  • Question Question
VBA help: Removing Patient from list that has Discharged, and for non-discharged getting the time period
Replies
1
Views
215
eiloken
E

Forum statistics

Threads
1,224,616
Messages
6,179,908
Members
452,949
Latest member
beartooth91

Share this page

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