FORMULA To Find and Extract a Date & Time where Description ID is in list of Item

L

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
240
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
HI Friend

i need an assistance with a formula to to Search and find the date & time where Materials ID

In Column G, i Have DESCRIPTION of ITEM Listed
In Column I, i have Date & TIme Listed
In Column O, I have The Description of Item Listed also As Criteria
I need to help with formula to Search Date & Time when the Criteria In Column O if There in Column G then to give the Date & Time in column P

I have place an Example Highlighted Appreciate if i could get a better way and formula to extract it all

Thank

LIFTING INSPECTION RELEASED DETAILS JAN-2022 to 23 MAY 2022.xlsx
GHIJKLMNOP
3DESCRIPTIONCATEGORYDate Inspected Vessel Name Date Rig Name Supplier Name DESCRIPTION Cargo IDSEARCH RESULT Date
4TAC 18-03C01/01/2022 09:00:00SEACOR OHIO1-Jan-22Barge Al HylehADNHAF-3
59990BD10664221C01/01/2022 09:40:00SEACOR OHIO1-Jan-22Barge Al HylehADNHAC-20
6BUNDLE OF PIPEB01/01/2022 10:10:00SEACOR OHIO1-Jan-22Barge Al HylehADNHAC-36
7275C01/01/2022 13:50:00SEACOR OHIO1-Jan-22Barge Al HylehADNHAD-6
8282, 247C01/01/2022 13:50:00SEACOR OHIO1-Jan-22Barge Al HylehADNHAD-8
9DNVU-49175, DNVU-49155, DNVU-49191, DNVU-49103, DNVU-49123, DNVU-49125C01/01/2022 10:00:00SEACOR OHIO1-Jan-22Barge Al HylehADNOC OFFSHOREMTB-64-76
10BARGE OXY-07, BARGE OXY-29, BARGE OXY-26, ZK FBN-OXG-28C01/01/2022 12:30:00SEACOR OHIO1-Jan-22Barge Al HylehADNOC OFFSHOREMTB-64-81
116647-4921, SCB-064, TF-20F-020C01/01/2022 12:30:00SEACOR OHIO1-Jan-22Barge Al HylehADNOC OFFSHOREMTB-64-84
12TF-20F-043, SCB-011, ZL-316C01/01/2022 12:30:00SEACOR OHIO1-Jan-22Barge Al HylehADNOC OFFSHOREMTB-64-37
1335-34-04C01/01/2022 13:15:00SEACOR OHIO1-Jan-22Barge Al HylehADNOC OFFSHORE5123-0707
14LCB-006C01/01/2022 12:00:00SEACOR OHIO1-Jan-22Barge Al HylehADNOC OFFSHOREJO-Q-057
15ZTS-204C01/01/2022 12:00:00SEACOR OHIO1-Jan-22Barge Al HylehALPHAMED 251
169259/1-2,266135,266959C01/01/2022 13:10:00SEACOR OHIO1-Jan-22Barge Al HylehALPHAMED 266
17MB-BAL-04C01/01/2022 13:10:00SEACOR OHIO1-Jan-22Rig MarawwahADNOC L&SBULK
18A-301,A-302,A-303,A-106,A310,A-311,A-312,A-315,HB-003C01/01/2022 13:10:00SEACOR OHIO1-Jan-22Rig MarawwahADNOC L&SBULK
19GCA-307C01/01/2022 14:00:00SEACOR OHIO1-Jan-22Rig MarawwahADNOC L & S BULK
20GCA-102C01/01/2022 14:00:00SEACOR OHIO1-Jan-22Rig MarawwahADNOC OFFSHOREJU-321
21BF4012C075C01/01/2022 22:45:00SEACOR OHIO1-Jan-22Rig MarawwahADNOC OFFSHOREJU-445
22TRD-45668, 62499, 73154C01/01/2022 22:45:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-30101/01/2022 13:10:00
23GI OB 009C01/01/2022 20:40:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-30201/01/2022 13:10:00
2410 BUNDLES, 210299, 13519682, GYD B12, HAL 3340-3, HAL 3340-11, HAL 2735/2C01/01/2022 22:10:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-30301/01/2022 13:10:00
251479-01C01/01/2022 22:10:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-10601/01/2022 13:10:00
26TOTEST01/01/2022 22:10:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-31001/01/2022 13:10:00
27CBX 221C01/01/2022 22:10:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-31101/01/2022 13:10:00
28OGCN 20010C01/02/2022 09:50:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-31201/01/2022 13:10:00
29H 5151C01/02/2022 11:15:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAA-31501/01/2022 13:10:00
301702058-06, 3902-1, 3902-3C01/02/2022 11:15:00SEACOR OHIO1-Jan-22Rig MarawwahAL AHLIAHB-00301/01/2022 13:10:00
Sheet1
Cell Formulas
RangeFormula
I4:I30I4=CONCATENATE(TEXT(B4,"mm/dd/yyyy")&" "&TEXT(F4,"hh:mm:ss"))
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
I have it search for column O in column G. But since I don't want a partial match, I wrap commas around the search item and the search list. The first match will output the date.

MrExcelPlayground9.xlsx
GHIJKLMNOP
3DESCRIPTIONCATEGORYDate Inspected Vessel Name Date Rig Name Supplier Name DESCRIPTION Cargo IDSEARCH RESULT Date
4TAC 18-03C1/1/2022 8:00SEACOR OHIO44562Barge Al HylehADNHAF-3 
59990BD10664221C1/2/2022 8:00SEACOR OHIO44562Barge Al HylehADNHAC-20 
6BUNDLE OF PIPEB1/3/2022 8:00SEACOR OHIO44562Barge Al HylehADNHAC-36 
7275C1/4/2022 8:00SEACOR OHIO44562Barge Al HylehADNHAD-6 
8282, 247C1/5/2022 8:00SEACOR OHIO44562Barge Al HylehADNHAD-8 
9DNVU-49175, DNVU-49155, DNVU-49191, DNVU-49103, DNVU-49123, DNVU-49125C1/6/2022 8:00SEACOR OHIO44562Barge Al HylehADNOC OFFSHOREMTB-64-76 
10BARGE OXY-07, BARGE OXY-29, BARGE OXY-26, ZK FBN-OXG-28C1/7/2022 8:00SEACOR OHIO44562Barge Al HylehADNOC OFFSHOREMTB-64-81 
116647-4921, SCB-064, TF-20F-020C1/8/2022 8:00SEACOR OHIO44562Barge Al HylehADNOC OFFSHOREMTB-64-84 
12TF-20F-043, SCB-011, ZL-316C1/9/2022 8:00SEACOR OHIO44562Barge Al HylehADNOC OFFSHOREMTB-64-37 
1335-34-04C1/10/2022 8:00SEACOR OHIO44562Barge Al HylehADNOC OFFSHORE5123-0707 
14LCB-006C1/11/2022 8:00SEACOR OHIO44562Barge Al HylehADNOC OFFSHOREJO-Q-057 
15ZTS-204C1/12/2022 8:00SEACOR OHIO44562Barge Al HylehALPHAMED 251 
169259/1-2,266135,266959C1/13/2022 8:00SEACOR OHIO44562Barge Al HylehALPHAMED 266 
17MB-BAL-04C1/14/2022 8:00SEACOR OHIO44562Rig MarawwahADNOC L&SBULK 
18A-301,A-302,A-303,A-106,A310,A-311,A-312,A-315,HB-003C1/15/2022 8:00SEACOR OHIO44562Rig MarawwahADNOC L&SBULK 
19GCA-307C1/16/2022 8:00SEACOR OHIO44562Rig MarawwahADNOC L & S BULK 
20GCA-102C1/17/2022 8:00SEACOR OHIO44562Rig MarawwahADNOC OFFSHOREJU-321 
21BF4012C075C1/18/2022 8:00SEACOR OHIO44562Rig MarawwahADNOC OFFSHOREJU-445 
22TRD-45668, 62499, 73154C1/19/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-3011/15/2022 8:00
23GI OB 009C1/20/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-3021/15/2022 8:00
2410 BUNDLES, 210299, 13519682, GYD B12, HAL 3340-3, HAL 3340-11, HAL 2735/2C1/21/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-3031/15/2022 8:00
251479-01C1/22/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-1061/15/2022 8:00
26TOTEST1/23/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-310 
27CBX 221C1/24/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-3111/15/2022 8:00
28OGCN 20010C1/25/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-3121/15/2022 8:00
29H 5151C1/26/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAA-3151/15/2022 8:00
301702058-06, 3902-1, 3902-3C1/27/2022 8:00SEACOR OHIO44562Rig MarawwahAL AHLIAHB-0031/15/2022 8:00
Sheet8
Cell Formulas
RangeFormula
P4:P30P4=IF(MIN(IF(ISERROR((SEARCH(","&O4&",",","&$G$4:$G$30&","))),"",ROW($G$4:$G$30)-ROW($G$4)+1))>0,INDEX($I$4:$I$30,MIN(IF(ISERROR((SEARCH(","&O4&",",","&$G$4:$G$30&","))),"",ROW($G$4:$G$30)-ROW($G$4)+1))),"")
 
Upvote 0
Hi James

Thanks so much really makes my work lot easy

i really appreciate

please do have nice wkend
 
Upvote 0
Lukma said:
Thanks so much really makes my work lot easy
Click to expand...
Assuming that your sample data is realistic, then are you sure that suggestion works?
For example, I have altered cell O6 in the sample below and the suggested formula (column Q) is not picking up the match in row 11. Part of the problem is that your sample data is inconsistent. Some of the IDs in column G are joined only by commas but others are joined by comma-space.

I think that my simpler suggested formula in column O allows for your inconsistent data but the formula could be even simpler if your data was consistent.

22 05 28.xlsm
GIJOPQ
2Peter_SSsJamesCanale
3DESCRIPTIONDate Inspected DESCRIPTION Cargo IDSEARCH RESULT Date SEARCH RESULT Date
4TAC 18-031/1/2022 8:00AF-3  
59990BD106642212/1/2022 8:00AC-20  
6BUNDLE OF PIPE3/1/2022 8:00SCB-0648/1/2022 8:00 
72754/1/2022 8:00AD-6  
8282, 2475/1/2022 8:00AD-8  
9DNVU-49175, DNVU-49155, DNVU-49191, DNVU-49103, DNVU-49123, DNVU-491256/1/2022 8:00MTB-64-76  
10BARGE OXY-07, BARGE OXY-29, BARGE OXY-26, ZK FBN-OXG-287/1/2022 8:00MTB-64-81  
116647-4921, SCB-064, TF-20F-0208/1/2022 8:00MTB-64-84  
12TF-20F-043, SCB-011, ZL-3169/1/2022 8:00MTB-64-37  
1335-34-0410/1/2022 8:005123-0707  
14LCB-00611/1/2022 8:00JO-Q-057  
15ZTS-20412/1/2022 8:00251  
169259/1-2,266135,26695913/1/2022 8:00266  
17MB-BAL-0414/1/2022 8:00BULK  
18A-301,A-302,A-303,A-106,A310,A-311,A-312,A-315,HB-00315/1/2022 8:00BULK  
19GCA-30716/1/2022 8:00BULK  
20GCA-10217/1/2022 8:00JU-321  
21BF4012C07518/1/2022 8:00JU-445  
22TRD-45668, 62499, 7315419/1/2022 8:00A-30115/1/2022 8:0015/1/2022 8:00
23GI OB 00920/1/2022 8:00A-30215/1/2022 8:0015/1/2022 8:00
2410 BUNDLES, 210299, 13519682, GYD B12, HAL 3340-3, HAL 3340-11, HAL 2735/221/1/2022 8:00A-30315/1/2022 8:0015/1/2022 8:00
251479-0122/1/2022 8:00A-10615/1/2022 8:0015/1/2022 8:00
26TOTES23/1/2022 8:00A-310  
27CBX 22124/1/2022 8:00A-31115/1/2022 8:0015/1/2022 8:00
28OGCN 2001025/1/2022 8:00A-31215/1/2022 8:0015/1/2022 8:00
29H 515126/1/2022 8:00A-31515/1/2022 8:0015/1/2022 8:00
301702058-06, 3902-1, 3902-327/1/2022 8:00HB-00315/1/2022 8:0015/1/2022 8:00
Date Time
Cell Formulas
RangeFormula
P4:P30P4=IFERROR(AGGREGATE(15,6,I$4:I$30/ISNUMBER(SEARCH(","&O4&",",","&SUBSTITUTE(G$4:G$30,", ",",")&",")),1),"")
Q4:Q30Q4=IF(MIN(IF(ISERROR((SEARCH(","&O4&",",","&$G$4:$G$30&","))),"",ROW($G$4:$G$30)-ROW($G$4)+1))>0,INDEX($I$4:$I$30,MIN(IF(ISERROR((SEARCH(","&O4&",",","&$G$4:$G$30&","))),"",ROW($G$4:$G$30)-ROW($G$4)+1))),"")
 
Upvote 0
Solution
Hi Peter

That Amazing i was also in thought of that since the Actual data is from other department, after dragging the formula down i notice is not picking most information date, after trying today at office before seeing your reply

both are amazing i will be going for Peter option in other to capture missing date


many thanks to you both

Regards
 
Upvote 0
You're welcome. Thanks for the follow-up. :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
suggest excel formula to calculate cumulative interest on no of days etc ......
Replies
1
Views
208
Peter_SSs
Peter_SSs
F
  • Question
Need to find and highlight values from high column which are greater than 10 times low value but on same expiry date,same strike price,same option typ
Replies
3
Views
299
etaf
etaf
A
  • Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
124
Abdo
A
Chris_010101
  • Solved
Sum columns on multiple sheets
Replies
3
Views
303
Leo Skywalker
L
G
  • Question
Formula to Retrieve uniqueID row Above
Replies
1
Views
346
Trebor76
T

Forum statistics

Threads
1,214,641
Messages
6,120,693
Members
448,979
Latest member
DET4492

Share this page

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
Back
Top