Find Last JOB ID by Date

[sbara1]

I've been using a vlookup formula that takes several steps to remove repeated jobs that have the same Order # but earlier CLRDDATE's.

I need a better way to capture JOB ID of the last or most recent date of the "Duplicate" ORDER #'s in my sample below.

There's serveral different dates of the same order number(C2380140) and each has a separate JOB ID. I need a way to extract the JOB ID C2004950 that was done on 20070411 to a different column.

Hope someone can help me make my job easier.

JOB ID ORDER # CKT_ID ID DATE TIME
C2002570 C2380140 9998876363 24444 20070404 1700
C2004950 C2380140 9998876363 24455 20070411 1600
C1987550 C2768490 9998859809 21402 20070207 1630
C1981320 C2774340 9999340690 21407 20070112 1245
C1977800 C2837241 9999666547 21406 20070102 1500
C1983060 C2853500 9999297490 21480 20070122 0900
C1979720 C2380140 9998876363 21461 20070110 1530
C2014520 C2879460 9999642057 21405 20070507 1600

 

[Richard Schollar]

Hi & Welcome to the Board!

Does this work for you? It is an array formula that must be entered with Ctrl+Shift+Enter:

Excel Workbook
	A	B	C	D	E	F
1	JOB ID	ORDER #	CKT_ID	ID	DATE	TIME
2	C2002570	C2380140	9998876363	24444	20070404	1700
3	C2004950	C2380140	9998876363	24455	20070411	1600
4	C1987550	C2768490	9998859809	21402	20070207	1630
5	C1981320	C2774340	9999340690	21407	20070112	1245
6	C1977800	C2837241	9999666547	21406	20070102	1500
7	C1983060	C2853500	9999297490	21480	20070122	900
8	C1979720	C2380140	9998876363	21461	20070110	1530
9	C2014520	C2879460	9999642057	21405	20070507	1600
10
11	Order #	Job ID
12	C2380140	C2004950
Sheet2

 

[sbara1]

Aloha and Mahalo much!

Wow, that was fast! My wife from Japan said you must be real kind because most English folks would say I don't want to help a Yankee. haha
I think she was influenced by a few New Zealand and Auzzie's. Appreciate you taking the time to help make Excel life easier.

Aloha,

Stan

 

[Richard Schollar]

You're welcome Stan

I like Americans - roasted slow and covered in a hot chipotle sauce

Seriously, you will find people from all over the world ready to help others out on this board - it's part of what makes it such a great community!