Lookup?
 

I have 5 columns of data and I want to write a function to (find) display the
earliest (Min) and Latest (Max) dates for each set of Item #'s.
If I sort by Item #'s how can I display the Max and Min dates for each of
the items

My data consist of about 150 separate items with an average of 50 entry's
per item.

Item Description Name Hours Date
13 Job1 C. Smith 5.00 10/01/07
13 Job1 D.Jones 8.00 10/02/07
13 Job2 F. Bing 6.00 10/01/07
14 Job1 C. Smith 8.00 9/29/07
14 Job1 D. Jones 7.00 9/28/07
15 Job2 F. Bing 6.00 8/31/07
15 Job2 C. Smith 4.00 8/30/01

Lookup?
 

One way...

Assume A1:E1 are column headers

Add these column headers to G1 and H1:

Min Date, Max Date

Enter these array formulas** in G2 and H2:

G2: =IF(A1=A2,"",MIN(IF(A$2:A$8=A2,E$2:E$8)))
H2: =IF(A1=A2,"",MAX(IF(A$2:A$8=A2,E$2:E$8)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Select both G2 and H2 and copy down as needed.

Format the cells as DATE

--
Biff
Microsoft Excel MVP


"Drydock" wrote in message
...
I have 5 columns of data and I want to write a function to (find) display
the
earliest (Min) and Latest (Max) dates for each set of Item #'s.
If I sort by Item #'s how can I display the Max and Min dates for each of
the items

My data consist of about 150 separate items with an average of 50 entry's
per item.

Item Description Name Hours Date
13 Job1 C. Smith 5.00 10/01/07
13 Job1 D.Jones 8.00 10/02/07
13 Job2 F. Bing 6.00 10/01/07
14 Job1 C. Smith 8.00 9/29/07
14 Job1 D. Jones 7.00 9/28/07
15 Job2 F. Bing 6.00 8/31/07
15 Job2 C. Smith 4.00 8/30/01