IF or FILTER statement to decide if a Cell and its length Falls between ranges

[Jn12345]

The title is probably not the best way of describing what im trying to ask for. Basically I have defect measurements (Start (m) and Length (mm)) and i need to determine if any part of the length from start to finish of the defect falls under different sleeve areas. (To be considered under one of these areas there is a criteria which will make the defect repair status show SLEEVED or N/A) that part is completed on my actual template so this is how ive tried to do this in the attached. Basically I'm just trying to put a formula in the column for additional defects under sleeve so that it can auto calculate this field. Sorry in advance if this makes no sense. Please see attached. (In summary im basically trying to figure out which defects land under the sleeve areas with the criteria of being covered by 50mm on each side but the sleeves can be butted up together to make a longer sleeve that will count as covering the defects (in the case of MFG-001) and i can easily figure out how to determine the first sleeve covering the defect and the last but on a long defect that is covered by multiple sleeves i cant get it to read all sleeves in between as covering the defect. Basically if it has passed the criteria of being SLEEVED then it just needs to figure out which sleeves it is running under. (this is for defects on a pipeline that will be covered by metal sleeves to repair them)

[#2]

@Jn12345 In simple, are you trying to omit Column K? If so, then you may try-

=TEXTJOIN(" ",TRUE,UNIQUE(FILTER(Table2[Defect ID],MAP(Table2[Axial Start (m)],Table2[Repair Status],LAMBDA(x,y,IF(y="SLEEVED",XLOOKUP(x,[Axial Start (m)],[Sleeve ID],"",-1,1),"")))=A3,"")))

See the attached file. You can clear Column K. 

[#3]

@Jn12345 

a sql test

//select * from length_Falls_between_ranges limit 20;
create temp table aa as
select iif(lead(f02)over() is null,200,lead(f02)over()) high ,f01,f02,f03,f04 from length_Falls_between_ranges where f01!="";
create temp table bb as
select f07,f08,f09,f10 from length_Falls_between_ranges where f07!="";
cli_no_header;
select f01,f02,f03,f04,(select group_concat(f07) from bb where bb.f08 between aa.f02 and high and f10 like 'SLEEVED') from aa;

 

[#4]

@Harun24HR Thanks for the response. It was close and you have actually come up with something similar to what i came up with. The only issue is it doesnt account for long defects that continue under multiple sleeves. This is accounting for the start of the defect which sleeve it would be under whereas i need to account for any sleeve that the defects that are "SLEEVED" fall under from the axial start including their length so thats why its so complicated. MFG-001 as it goes under multiple sleeves should be shown as additional defects under a few sleeves there. I originally made another column for defect axial start plus length to find the lookup for the sleeve end so that covers me for defects that are greater than 1 sleeve long when the sleeves are butted up end to end but it still doesnt account for if the defect is so long that it falls under 3+ sleeves. basically i need the formula to be nearly as simple as if the defect (from start to end ( so from start plus the length /1000 to get length in m)) if any part of that defect falls under a sleeve and has SLEEVED in the repair decision column i need to show all the sleeves that the defect falls under so that on the sleeve page each sleeve shows which defects the sleeves are covering. Its really quite complicated because it adds another element of length into the equation. not just starting point. i was thinking of making some big formula that checks which sleeve the axial start is under then 1/100th of the length plus axial start then 1/90th plus axial start and so on until ive covered the whole defect length but thats quite cumbersome and i was hoping someone would have a simpler answer. Thanks for the help and sorry that its so complicated! 

[#5]

Unfortunately im not an excel guru so i dont quite understand this. im sorry! basically i just need to find a way to do a simple if any part of defect length (from ax start to ax end) falls under any of the sleeve areas and also has "Sleeved" in the defect repair column then show which sleeves the entire length of the defect falls under. Sorry its so complicated. im sure theres a better way to do it that what im explaining

[#6]

This is accounting for the start of the defect which sleeve it would be under whereas
please refer to below

https://b23.tv/4FbSRks

The only issue is it doesnt account for long defects that continue under multiple sleeves.
Can you share your workbook file.and expected result?
I can not get what you.mean about long defects .

[#7]

Basically that I understand if the defect starts falls under the sleeve length than its easy to say which sleeve it is under and also the end but i need to figure out if any part of the defect from start to end falls under the sleeve length. If the defect start is 1m and the length is 10000mm (10m) and 5 sleeves cover the defects and are each 2 or so m in length i can get SLV-001 and SLV-005 (the first and last sleeves to show theyre covering the defect but then SLV-002 SLV-003 SLV-004 do not show that they are covering the defect also. (For long defects)

[#8]

@peiyezhu 

Thank you for your help. please see attached. I hope this helps explain. you can see some of the defects in red under some of the sleeves in black on the pipeline that the defects are found on in grey. You can see that MFG-001 is covered under multiple sleeves so if it is considered SLEEVED (being covered by at least 50mm on each end of the defect then the repair type is SLEEVED so if it has that critera of being repaired by sleeved in the repair type column then any sleeve it falls under should see it as a defect that is under the sleeve. Please see attached and i hope this explains. also on the attached excel sheet i have attached another table below for what it should look like with proper formulas. Basically the formula statement would be to say the defect is an associated defect if it meets two criteria. the defect says SLEEVED in the repair type column and if any part of the defects length from start to end falls underneath the sleeve area. 

[#9]

@Jn12345 

From the image I got it more clearly.
There are some SLEEVED(I consider them as some metal pieces or parts maned SLV-004
SLV-005
SLV-006)
.
And you do detect on them.
The most of defects are very small size which like below 100mm we call poit defects because of the small size.
But MFG-001 is an exception which size is 10000mm so that it cover several pieces of parts or SLEEVES (cover from begin of SLV-004 through
SLV-005 until the end of
SLV-006.
)
I can understand this point from the SLEEVE Defects2.png.

the below table Range B20:E28 will be your expected result right?

if yes,
SLV-004 6.7
SLV-007 19 20
13.3=20-6.7

only 13.3mm not exceed 100000mm,why not in MFG-001

why

SLV-006 13 18 MFG-001

SLV-007 19 20

SLV-007 NOT be.covered by MFG-001.

The image shows SLV-006 has long size (more than 900mm from.the image rather than 5mm(18-13=5mm range C26-B26)

So I feel.confused with your datas or expected result in Sheet1.

they are not consist with the image(see my image )

 

So,can.you confirm your expected result without SLV-007 MFG-001 covered?