Forum Discussion
IF or FILTER statement to decide if a Cell and its length Falls between ranges
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)
8 Replies
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;- 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
- 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 .
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.
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!
