Custom Look Up ?

JonathanRichardson

New Member
Joined
Aug 23, 2006
Messages
2
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDE
1Job NumberStatus date timeJob NumberStatus date time
26639705302/09/06 16:156639705302/09/06 16:18
36639705302/09/06 16:186821047302/08/06 13:00
46639705302/09/06 15:486828357102/07/06 13:48
56639705302/08/06 15:08
66821047302/08/06 13:00
76821047302/08/06 2:21
86821047302/08/06 5:28
96828357102/07/06 13:48
106828357102/07/06 11:28
116828357102/07/06 8:00
Sheet2
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top