Need A Help With My formula

L

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Friends

Good Day Need your assistance with my formula in "column R9" I was able to extract, but I need the formula to extract line up serial without jumping cells when i drag down
Appreciate a help or better way to go around the formula

Regards



Tonnage ata0Report may 0814hrs.xlsx
CDEFGHIJKLMNOPQR
7DSSV
8Vessel NameJanFebMarAprMayJunJulAugSepOctNovDecJanMonthly Drilling Vessel
9ADNOC-221NAFNAFNAFNAF   
10ADNOC-223NAFDSSVNAFNAF  
11ADNOC-224DSSVNAFNAFNAF0ADNOC-224
12ADNOC-226NAFNAFNAFNAF  
13ADNOC-227NAFNAFNAFNAF  
14ADNOC-228NAFNAFNAFNAF  
15ADNOC-229NAFNAFNAFNAF  
16ADNOC-230NAFNAFNAFNAF  
17ADNOC-510RMRMRMRM  
18ADNOC-511RMRMRMRM  
19ADNOC-512RMRMRMRM  
20ADNOC-810PSSVPSSVPSSVPSSV  
21ADNOC-811PSSVPSSVPSSVPSSV  
22ADNOC-812PSSVPSSVPSSVPSSV  
23ADNOC-850PSSVPSSVPSSVPSSV  
24ADNOC-851PSSVPSSVPSSVPSSV  
25ADNOC-A02DSSVDSSVNAFNAF0ADNOC-A02
26ADNOC-A03NAFNAFNAFNAF  
27ADNOC-A04DSSVNAFNAFNAF0ADNOC-A04
28ADNOC-A05DSSVDSSVDSSVPSSV0ADNOC-A05
29ADNOC-A07  
30ADNOC-A08 
31ADNOC-S02NAFNAFNAFNAF 
32ADNOC-S03 
33A-CHLOEDSSVDSSVDSSVDSSV 
34A-GRACEDSSVDSSVDSSVDSSV 
35A-LIBERTYDSSVDSSVDSSVDSSV 
36AMIL-17DSSVDSSV 
37AMS-ONYXDSSVDSSVDSSVDSSV 
38AMS-RUBYDSSVDSSV 
39A-RADIANT-7PSSVPSSVPSSVPSSV 
40A-RANGERDSSVDSSVDSSVDSSV 
41ATLAS-SAPPHIREDSSVDSSVDSSVDSSV 
42B-GULF-101DSSVDSSVDSSVDSSV 
43Cancelled 
44CECILIE-KDSSVDSSVDSSVDSSV 
45CORALDSSVDSSVDSSVDSSV 
46CREST RADIANT-5PSSVPSSVPSSVPSSV 
47CREST VICTORIA 
48JOPETWIL-70PSSVPSSVPSSVPSSV 
49LCT AD-ASTRADSSVDSSVDSSV 
50LCT ADNOC-1010DSSVPSSVDSSV 
51LCT ADNOC-1011PSSV 
52LCT ARADAH 
53LCT BIMADSSVDSSVDSSVDSSV 
54LCT BUSHRAPSSVPSSVPSSV 
55LCT HOREYEH 
56LCT JAMELAH 
57LCT JEWAHER-1DSSVDSSV 
58LCT JOPETWIL-62DSSVDSSVDSSV 
59LCT JOPETWIL-68DSSVDSSV 
60LCT JOPETWIL-71PSSVPSSVPSSV 
61LCT KENOOZ 
62LCT MARIYAMPSSV 
63LCT MARWAH-1PSSVPSSVPSSVPSSV 
64LCT NASAYEM -1 
65LCT PIONEER 
66LCT RAZAN-1PSSVPSSVPSSVPSSV 
67LCT SEA PARROTDSSVPSSVPSSVPSSV 
68LCT SHEWELEHPSSVPSSVPSSVDSSV 
69LCT TAIBAH - 1 
70LCT TAMARAPSSVPSSV 
71LCT TARFFAH-1PSSVPSSVPSSVPSSV 
72LCT THURAYA 
73LCT WARDEH-1DSSVDSSVDSSV 
74LCT-JARYEH-1 
75LCT-JEWAHER-1DSSVDSSV 
76LCT-TARFFAH-1 
77MARCAP-2 
78MARCAP-203NAFNAFNAFNAF 
79MARSOL SCAMANDERDSSVDSSVDSSVPSSV 
80M-SUPPORTERDSSVDSSVDSSVDSSV 
81MUBARAK SPIRIT 
82MUTAWA-103 
83MUTAWA-304PSSVPSSVPSSVPSSV 
84MUTAWA-306PSSVPSSVPSSVPSSV 
85MUTAWA-402DSSVDSSVDSSVDSSV 
86PIONEER EAGLE 
87QMS CARDINAL 
88QMS NEPTUNE 
89REVELATIONDSSVDSSV 
90SK MAINSTAY 
91SMIT LUMUTRMRMRMRM 
92SMIT LUZONRMRMRMRM 
93STANFORD ALPHADSSVDSSVDSSVDSSV 
94SWISSCO RUBYPSSVPSSVPSSVPSSV 
95VENTUREDSSVDSSVDSSVDSSV 
96Z-EMPEROR 
97Z-OCEANPSSVPSSVPSSVPSSV 
98Z-POWER 
99Z-QUEENPSSVPSSVPSSVPSSV 
100ADNOC-222 
101ADNOC-225 
102Z-STAR 
103ADNOC-513 
104ADNOC-952 
105ADNOC-S01 
Sheet2
Cell Formulas
RangeFormula
I9I9=IFERROR(VLOOKUP(C9,$R$9:$S$196,2,0),"")
R9:R29R9=IF($Q$7="DSSV",IF(INDEX($D9:O9,MATCH($Q$8,$D$8:$O$8,0))="DSSV",INDEX($C9:$C13,MATCH("DSSV",$D9:$O9,0)),""), IF($Q$7="RM",IF(INDEX($D9:$O9,MATCH($Q$8,$D$8:$O$8,0))="RM",INDEX($C$9:$C$13,MATCH("RM",$D9:$O9,0)),"")))
H9:H105H9=IFERROR(VLOOKUP(C9,$R$9:$S$196,2,0),"")
 

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
Try this in R9 and filldown.
Excel Formula: 
=IFERROR(
     INDEX($C$9:$C$105,
          AGGREGATE(15, 6,
               (ROW(INDEX($D$9:$O$105,0,MATCH($Q$8,$D$8:$O$8,0)))-ROW($C$9)+1)/(INDEX($D$9:$O$105,0,MATCH($Q$8,$D$8:$O$8,0))=$Q$7),
                     ROW(1:1))),
  "")
 
Upvote 0
Solution
Cubist said:
Try this in R9 and filldown.
Excel Formula: 
=IFERROR(
     INDEX($C$9:$C$105,
          AGGREGATE(15, 6,
               (ROW(INDEX($D$9:$O$105,0,MATCH($Q$8,$D$8:$O$8,0)))-ROW($C$9)+1)/(INDEX($D$9:$O$105,0,MATCH($Q$8,$D$8:$O$8,0))=$Q$7),
                     ROW(1:1))),
  "")
Click to expand...
Hi Cubist supper is great just what I need many thanks
but in the last Formula ROW(1:1) what does this signify in the formula as it highlights all Row

Appreciate if I could know

Regards
 
Upvote 0
Lukma said:
Hi Cubist supper is great just what I need many thanks
but in the last Formula ROW(1:1) what does this signify in the formula as it highlights all Row

Appreciate if I could know

Regards
Click to expand...
It's a way to generate sequences in older versions of Excel. Row(1:1) gives you 1, then fill down to next row is Row(2:2) -> 2 and so on.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
318
etaf
etaf
L
  • Solved
Formula in the helper column. need duplicate to be count as One
Replies
7
Views
510
KRice
K
L
  • Question Question
Help with formula to keep leading zero in cell - cant format as date
Replies
4
Views
72
6StringJazzer
6StringJazzer
P
  • Question Question
Creating Sub Totals for a Sort, Unique, Filter, Choose Cols dynamically
Replies
6
Views
350
ExcelToDAX
E
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

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