Custom Look Up ?

[JonathanRichardson]

I have two columns, one is “job numbers” with a eight digit number, the other has “Status date time” with the format as “13/09/2006 16:15:00”

My problem is that the job numbers show each time the status changes. So the “job numbers” column has 6278 rows but only 1400 unique jobs with 6278 status changes.

I want to end up with 1400 rows showing the date and time of the last status change?


Job Number Status date time
66397053 13/09/2006 16:15
66397053 12/09/2006 16:18
66397053 05/09/2006 15:48
66397053 29/08/2006 15:08
68210473 03/08/2006 13:00
68210473 02/08/2006 02:21
68210473 01/08/2006 05:28
68283571 25/07/2006 13:48
68283571 24/07/2006 11:28
68283571 23/07/2006 08:00

Example

Job Number Status date time
66397053 13/09/2006 16:15
68210473 03/08/2006 13:00
68283571 25/07/2006 13:48


Thanks

 

[fairwinds]

Hi,

Try:

D1: =INDEX(A2:$A$100,MATCH(0,--ISNUMBER(MATCH(A2:$A$100,$D$1:D1,0)),0))

E1: =MAX(IF($A$2:$A$100=D2,$B$2:$B$100))

Both confirmed with Ctrl + shift + enter before being dragged down.

Book1
	A	B	C	D	E
1	Job Number	Status date time		Job Number	Status date time
2	66397053	02/09/06 16:15		66397053	02/09/06 16:18
3	66397053	02/09/06 16:18		68210473	02/08/06 13:00
4	66397053	02/09/06 15:48		68283571	02/07/06 13:48
5	66397053	02/08/06 15:08
6	68210473	02/08/06 13:00
7	68210473	02/08/06 2:21
8	68210473	02/08/06 5:28
9	68283571	02/07/06 13:48
10	68283571	02/07/06 11:28
11	68283571	02/07/06 8:00
Sheet2