Lookup Adjacent Cell Values based on Newest and Oldest Date

Hi There,

I have been going nuts for a day or two trying to figure this oout and am not having any luck. I'm hoping some of the experts here might be able to make a suggestion or two for how I can make this work.

In a nut shell, I recieve a report weekly that lays out what my company calls "incidents" and then a bit of detail for each one. One of the reports I then have to present, based on that data, is the details for the newest and the oldest incident for each agent. At the moment I am doing it manually and it is a royal pain in the butt. I am trying to find a way to get Excel to automate it for me.

So, the raw data I get has the incident number in Column A, Agent name in Column B, Code in Column C, the Date in D, the Age in E, and lastly the Status in F. In the chart I've built I have each agent's name in Column A. I am looking for a formula to go into B through G that will filter through the raw data, find the oldest incident for each agent and give me the details, then do say again in H through K for the newest incident.

For example, going by the dummy data that I've pasted below, for Agent One the columns would populate thus:
B: 8 (Oldest Incident for Agent One)
C: RJS (Incident 8s Code)
D: 6/4/2008 (Incident 8s Date)
E: 84896 (Incident 8s Age)
F: Open (Incident 8s Status)
G: 12 (Newest Incident for Agent One)
H: 1S8 (Incident 12s Code)
I: 6/4/2008 (Incident 12s Date)
J: 9640 (Incident 12s Age)
K: Open (Incident 12s Status)

I'm pasting a small piece of the raw data below. It's only the first 24 lines and has 6 agents in all. The actual data I am recieving is for roughly 150 agents and has 2000 - 3000 incidents on average.

I'm not sure how readable this will be here, though. There are 6 columns in all:
Column A: Incdnt
Column B: Agent
Column C: Code
Column D: Date
Column E: Age
Column F: Status


Here is the data:

Incdnt Agent Code Date Age Status
1 Agent One 1UB 5/30/2008 14010 Open
2 Agent Three G9M 6/3/2008 8313 Open
3 Agent Four HKX 6/5/2008 68958 Open
4 Agent Four MEV 5/26/2008 157949 Open
5 Agent Three 1FV 6/6/2008 24271 Open
6 Agent Five DY2 5/29/2008 38705 Open
7 Agent Six 63T 6/3/2008 44465 Assigned
8 Agent One RJS 6/4/2008 84896 Open
9 Agent Four OM3 6/3/2008 8236 Assigned
10 Agent Two D7L 6/6/2008 8670 Hold
10 Agent Six WYW 6/6/2008 5615 Open
11 Agent Six VF6 6/6/2008 3914 Open
12 Agent One 1S8 6/3/2008 9640 Open
13 Agent Four 2T1 6/6/2008 24211 Open
14 Agent Five K1B 6/6/2008 4068 Open
15 Agent Two B4O 6/6/2008 8567 Hold
16 Agent Five 13U 6/6/2008 3986 Open
17 Agent Six V4H 5/27/2008 165041 Open
18 Agent Five CDN 5/28/2008 16854 Open
19 Agent Three RS6 5/30/2008 745697 Hold
21 Agent Three DXR 6/6/2008 24036 Hold
22 Agent Four 5WE 6/6/2008 110747 Open
23 Agent Five S59 6/5/2008 34441 Open
24 Agent Six N7G 5/30/2008 14229 Open


If ayone can help me with this, I would really, really appreciate it. This has been driving me absolutely insane...

LOL

Thanx a million in advance!!


Nevi

See if the attached is what you are looking for...

Sheet 1 has raw data... Sheet2 has summary

Note: I added column A in Sheet2 to list the Agents and the formulas refer to column A to determine which Agent info to extract...

The formulas are array formulas and once you make changes, you must re-confirm them with Ctrl+Shift+Enter not just enter, then copy them down.

If you switch columns A and B around in Sheet1, then you could enter the formula once for Oldest and once for Newest and copy over 4 columns respectively.