lookup or match or max???

hi there
XL - 2000
i have a page in my workbook with the following info:

Col A Col O Col P
Name From To
Bob Jones 01-Dec-03 01-Jan-04
Bob Jones 02-Jan-04 02-Feb-04
Bob Jones 03-Feb-04 05-Mar-04
Dave Smith 06-Mar-04 06-Apr-04
Dave Smith 07-Apr-04 08-May-04
Dave Smith 09-May-04 09-Jun-04
Alex Thomas 10-Jun-04 11-Jul-04
Alex Thomas 12-Jul-04 12-Aug-04
Alex Thomas 13-Aug-04 13-Sep-04


i need 3 lots of info from this book
(using bob as example)

Bobs earliest start date (01-Dec-03)
Bobs earliest end date (01-Jan-04)
Bobs latest end date (05-Mar-04)

any help - always gratefully received, am having complete 'blonde'
day.....


i


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

"buffyslay" wrote in message
...

hi there
XL - 2000
i have a page in my workbook with the following info:

Col A Col O Col P
Name From To
Bob Jones 01-Dec-03 01-Jan-04
Bob Jones 02-Jan-04 02-Feb-04
Bob Jones 03-Feb-04 05-Mar-04
Dave Smith 06-Mar-04 06-Apr-04
Dave Smith 07-Apr-04 08-May-04
Dave Smith 09-May-04 09-Jun-04
Alex Thomas 10-Jun-04 11-Jul-04
Alex Thomas 12-Jul-04 12-Aug-04
Alex Thomas 13-Aug-04 13-Sep-04


i need 3 lots of info from this book
(using bob as example)

Bobs earliest start date (01-Dec-03)
Bobs earliest end date (01-Jan-04)
Bobs latest end date (05-Mar-04)

any help - always gratefully received, am having complete 'blonde'
day.....

Try these array formulas, with ranges adjusted to suit your data:
=MIN(IF(A1:A100="Bob Jones",O1:O100))
=MIN(IF(A1:A100="Bob Jones",P1:P100))
=MAX(IF(A1:A100="Bob Jones",P1:P100))
Array formulas have to be entered using Ctrl+Shift+Enter rather than just
Enter.
If you wish, you could replace "Bob Jones" with a reference to a cell
containing "Bob Jones".

The easiest way is to use a pivot table (see bottom for
formula solution if you insist!):

**Pivot Table
1)Select a cell in the source data table
2) Select 'Pivot Table and Pivot Chart Report...' from
Data menu
3) Click Next
4) Click Next
5) Click Layout button
6) Drag the Name field button onto the row section
7) Drag the From field button onto the data section.
Double click it and select Min from the list of options
8) Drag the To field button onto the data section. Double
click it and select Min
9) Drag a second To field button onto the data section,
this time set as Max
10) click OK on this dialog, select where you want to put
the table (new sheet is usually best), and clcik Finish on
the main wizard dialog.
11) Right-click anywhere in the pivot table and select
Table Options from the shortcut menu. De-select Row and
Column Grand Totals as these are meaningless in this case.
Click OK.
12) Click and drag the Data field button on the pivot
table and drop over the 'Total' cell. This will align the
data into columns- usually this is best.
13) Right-click 'Min of From' cell and select Field
Settings from shortcut menu. Click Number.. and select the
required date format. Repeat for 'Min of To' and 'Max of
To'

Once set up, this pivot table can be used to summarise any
new data:
- If the data in the source data table changes, right
click in the pivot table and select Refresh Data
- If you add more data to the end of the source data table
then right-click in the pivot table and select Wizard.
Click Back, then update the source range in the box, then
click Finish. The new data will now be included.

**Formula solution:
(Assuming that the data is sorted by Name.)

Early Start:
=MIN(OFFSET($B$1,MATCH(Name,A:A,0)-1,0,COUNTIF
(A:A,Name),1))

Early End:
=MIN(OFFSET($C$1,MATCH(Name,A:A,0)-1,0,COUNTIF
(A:A,Name),1))

Late End:
=MAX(OFFSET($C$1,MATCH(Name,A:A,0)-1,0,COUNTIF
(A:A,Name),1))

Pivot tables get a bit of getting used to if you haven't
looked at them before, but are by far the most flexible
method for analysing this sort of data (IMHO). Let me know
if any clarification required.

Cheers,
Dave.
-----Original Message-----

hi there
XL - 2000
i have a page in my workbook with the following info:

Col A Col
O Col P
Name From To
Bob Jones 01-Dec-03 01-Jan-04
Bob Jones 02-Jan-04 02-Feb-04
Bob Jones 03-Feb-04 05-Mar-04
Dave Smith 06-Mar-04 06-Apr-04
Dave Smith 07-Apr-04 08-May-04
Dave Smith 09-May-04 09-Jun-04
Alex Thomas 10-Jun-04 11-Jul-04
Alex Thomas 12-Jul-04 12-Aug-04
Alex Thomas 13-Aug-04 13-Sep-04


i need 3 lots of info from this book
(using bob as example)

Bobs earliest start date (01-Dec-03)
Bobs earliest end date (01-Jan-04)
Bobs latest end date (05-Mar-04)

any help - always gratefully received, am having
complete 'blonde'
day.....


i


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.