Forum Discussion
Extract data using text within set of data
Hi i have a set of data with keywords containing SLS1,SLS2,SLS3....SLS10, ULS1,ULS2....ULS10.
i wish to segregate this into separate set of SLS and ULS using certain condition like max axial.
For ex. cell P8 should return the max axial value out of load case under column 'C' wherever the load has substring "SLS" in it.
Don't understand why my simple solution shouldn't work. Used Patrick's file to illustrate how it works.
16 Replies
You can do this with a simple formula approach.
If your load cases (with names like SLS1, SLS2, ULS1, etc.) are in column A, and the corresponding axial values are in column C, then:
For maximum SLS axial value, enter:
=MAX(FILTER(C:C,ISNUMBER(SEARCH("SLS",A:A))))
For maximum ULS axial value, enter:=MAX(FILTER(C:C,ISNUMBER(SEARCH("ULS",A:A))))
This way, Excel will look through column A for anything containing "SLS" (or "ULS") and return the maximum value from column C.If you’re on an older Excel version without FILTER, you can use an array formula (Ctrl+Shift+Enter):
=MAX(IF(ISNUMBER(SEARCH("SLS",A:A)),C:C))
This should give you exactly what you need in cell P8 for SLS, and similarly in another cell for ULS.Hi Riny, i tried your solution, see above reply to flexyourdata. it didnt work. i hope i applied correctly.
Don't understand why my simple solution shouldn't work. Used Patrick's file to illustrate how it works.
Another thing this formula works for extracting the max value for the given substring. if i use min instead of max, it is returning 0 instead of the value 17 (among all SLS). But if there is a -ve value it returns that. Can you please also check for that. Thank you.
min should give value from cell E7=17 but is returning 0.
if theres a negative value(E8) in the range, min returns that.
Please add an example file with the data in columns C through K.
the intent is that F4 should identify from col B which have substring "SLS" and return max value among those cases (SLS1,SLS2,SLS3,SLS4 etc). similarly for ULS
If the data is presented like this then you can roll it up with GROUPBY:
=GROUPBY(REGEXEXTRACT(name,"[a-z]+",1,1),val,MAX)If the data is more nuanced, then you'll need to upload an anonymized .xlsx.
Try this:
=MAX(ISNUMBER(FIND("SLS",C7:C5000))*F7:F5000)
