Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |