Returning a value from a table with multiple entries

[Neil]

Hi,

I have a table that has start times and finish times for different
people. I need to be able to return the value for the latest finish
time if there are multiple instances of that agent.

name start time finish time
agent 1 07:00 15:00
agent 2 08:00 08:15
agent 2 08:15 16:00
agent 3 09:00 17:00

In the above table i need to be able to return the value for agent 2
latest finish time. The vlookup formula i use returns the first value
it finds which is fine if there are only single entries.

Normally i would just sort the table and use the data, except i also
need to retrieve the earliest start time from the same table.

Any help would be greatly appreciated.
Thanks
Neil

 

[Max]

Assume your source table as posted in A1:C5
with real finish times in col C (recognized by Excel)

In E2 is the input for col A, eg: agent2
In say, F2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula:
=INDEX(C2:C5,MATCH(MAX(IF(A2:A5=E2,C2:C5)),IF(A2:A5=E2,C2:C5),0))
F2 will return the latest finish time (real times are numbers, hence the
latest time = max)
Adapt the ranges to suit the actual extents

 

[Harald Staff]

Hi Neil

The by far easiest way to do this is with a Pivot table. Set name as row
label, and Min of starttime & Max of finish time as values.

If you are unfamiliar with pivot tables, a complex formula solution may seem
more comfortable, but I urge you to spend 30 minutes on pivot tables, they
are perhaps the most useful and powerful feature of Excel, and really easy
and quick when you know how to.

HTH. Best wishes Harald

 

[Herbert Seidenberg]

Excel 2007 PivotTable
By far...
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_29_09.xlsx

 

[Neil]

Thanks Max, Harald, Herbert...

I will look into all of your options.

cheers

 

[Max]

I will look into all of your options ...

Kindly feedback further here on your learning experience with each response
that you received, and which route was finally adopted as the solution (and
why). This kind of feedback is rare, but vital.

 

[Max]

well, guess that was wishful thinking. it was a compelling post though.

 

[AdamV]

I would say VLOOKUP is right on the money for this. Sort the list by
agent and start time (as it seems you already do).
To get the first start time, use
VLOOKUP(<agent name>, <your range of data>, 2, FALSE)

To get the last finish time, use:
VLOOKUP(<agent name>, <your range of data>, 3, TRUE)

Notes:
- you can only use TRUE if your data is sorted (otherwise you get wrong
results)
- TRUE will match the last row which is less than or equal to the value
you are looking for. If your required value is not in the list you will
get a dud result. You could wrap an IF and a COUNT around (or a MATCH)
to prove the agent was in the list before doing the VLOOKUP, but my
assumption here is that since the first VLOOKUP would give you a #N/A
error, this would be enough to tell you that your agent name was wrong.
- I'm assuming yoru model is that one agent does jobs sequentially (as
it appears to be from your sample data), so start time of their third
job is the same or later than the end time of the second job. (it only
matters so that sorting by start time will also by definition be sorted
by finish time - if they could start job A at 9 am and finish at 5, but
start task B at 10 am and finish at 4, the sort order would be out of
whack).

HTH
Adam