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