Find Last JOB ID by Date

sbara1

New Member
Joined
Aug 1, 2007
Messages
6
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
ABCDEF
1JOB IDORDER #CKT_IDIDDATETIME
2C2002570C2380140999887636324444200704041700
3C2004950C2380140999887636324455200704111600
4C1987550C2768490999885980921402200702071630
5C1981320C2774340999934069021407200701121245
6C1977800C2837241999966654721406200701021500
7C1983060C285350099992974902148020070122900
8C1979720C2380140999887636321461200701101530
9C2014520C2879460999964205721405200705071600
10
11Order #Job ID
12C2380140C2004950
Sheet2
 

sbara1

New Member
Joined
Aug 1, 2007
Messages
6
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


:p
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
You're welcome Stan :biggrin:

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!
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

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