Lookup?

D

Drydock

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
 
T

T. Valko

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top