how do i find the earliest entry in a table

[MCscrewdriver]

Probably dead easy but its got me stumped.
Each week hours are booked to a number of different projects by 20 or more
people. I need to identify the first date any time was booked to a particular
project.
Column A is the list of dates and column B is the project reference number.
neither of the columns are in order.
once i get this sorted i then need to use different tables of rates
depending on the start date to calculate project costs (but more of that
later)

 

[Mike H]

Hi,

Try this with the project number your looking for in C1, change the ranges
to suit your data but they must all be the same size.

=MIN(IF(B1:B20=C1,IF(A1:A20<>"",A1:A20)))


This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Stefi]

=MATCH(MIN(A:A),A:A,0)
returns row number of the earliest date.

Regards,
Stefi

„MCscrewdriver†ezt írta:

 

[MCscrewdriver]

Thanks Mike,
Unfortunately this array formula only returns 0 so i think it must be down
to my initial explanation - i'll try again.

this is an excerpt of the table:

PA date Project
13-Jan-2006 R102
13-Jan-2006 R104
13-Jan-2006 R105
13-Jan-2006 R111
13-Jan-2006 R112
13-Jan-2006 R113
13-Jan-2006 R130
13-Jan-2006 E114
13-Jan-2006 E110
13-Jan-2006 E110
13-Jan-2006 E110

Projects may appear several times in any period due to a number of employees
booking time in the same week. In subsequent weeks the same projects will be
booked to and i need to determine the first time each project was booked to.
Hope this is clearer.

 

[Mike H]

Hi,

So I change you sample data to the table below and have E110 in C1. My
formula returns

13 - Jan - 2002

i.e the earliest dat for project E110

If your getting 0 then are you sure you array entered the formula correctly?
Read these instructions again and enter the formula as an array

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

13-Jan-2006 R102 E110
13-Jan-2006 R104
13-Jan-2006 R105
13-Jan-2006 R111
13-Jan-2006 R112
13-Jan-2006 R113
13-Jan-2006 R130
13-Jan-2006 E114
13-Jan-2006 E110
13-Jan-2002 E110
13-Jan-2006 E110

Mike

 

[MCscrewdriver]

Thanks Mike - problem sorted

Hi,

So I change you sample data to the table below and have E110 in C1. My
formula returns

13 - Jan - 2002

i.e the earliest dat for project E110

If your getting 0 then are you sure you array entered the formula correctly?
Read these instructions again and enter the formula as an array

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

13-Jan-2006 R102 E110
13-Jan-2006 R104
13-Jan-2006 R105
13-Jan-2006 R111
13-Jan-2006 R112
13-Jan-2006 R113
13-Jan-2006 R130
13-Jan-2006 E114
13-Jan-2006 E110
13-Jan-2002 E110
13-Jan-2006 E110

Mike

 

[Mike H]

Glad I could help

Thanks Mike - problem sorted