I have a list of items (Brine types) in column A. I am interesting in extracting the NaCl brine (A2:A18) only (Exclude all KCl brines that are in A19 to A32) then sort them in an ascending order.
I used the Let function to extract the NaCl brine in column C. Then I wanted to sort them as previously mentioned. to do this, I have to extract the numbers form the NaCl brines which I did in column E then I sort them sorted them using the Large function in column F then I used sortby function to finally sort the NaCl brine list in Column H
As you can see, I had to use several steps here to accomplish this but I couldn't use all these steps inside of the let function. the Variable name of the Brine list in the let function is Filetring_Brines (column C). when trying to use this variable with the byrow & Lambda to extract the numbers as in column D, i get an N/A.... Any idea how to solve this puzzle? I tried to use the Varibale filtering_Brines # (with Hashtag) inside the ByRow but didn't work.
the formula of column C is
=LET(Brine_Name,BYROW($A$2:$A$32,LAMBDA(x,LEFT(x,MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0)-2))),Brine_Condition,LEFT($C$1,MATCH(TRUE,ISNUMBER(--MID($C$1,ROW(INDIRECT("1:"&LEN($C$1))),1)),0)-2),filtering_Brines,FILTER($A$2:$A$32,Brine_Name=Brine_Condition),filtering_Brines)
the formula in column D for extracting the numbers from the filtering brines variable resulting from the above formula is
=LET(Brine_Name,BYROW($A$2:$A$32,LAMBDA(x,LEFT(x,MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0)-2))),Brine_Condition,LEFT($C$1,MATCH(TRUE,ISNUMBER(--MID($C$1,ROW(INDIRECT("1:"&LEN($C$1))),1)),0)-2),filtering_Brines,FILTER($A$2:$A$32,Brine_Name=Brine_Condition),Extract_Numbers,BYROW(filtering_Brines,LAMBDA(x,TRIM(MID(x,MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0),SEARCH("ppg",x)-MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0)))+0)),Extract_Numbers)
I Tried to use the XL2BB but the mini sheet options is not there.
I used the Let function to extract the NaCl brine in column C. Then I wanted to sort them as previously mentioned. to do this, I have to extract the numbers form the NaCl brines which I did in column E then I sort them sorted them using the Large function in column F then I used sortby function to finally sort the NaCl brine list in Column H
As you can see, I had to use several steps here to accomplish this but I couldn't use all these steps inside of the let function. the Variable name of the Brine list in the let function is Filetring_Brines (column C). when trying to use this variable with the byrow & Lambda to extract the numbers as in column D, i get an N/A.... Any idea how to solve this puzzle? I tried to use the Varibale filtering_Brines # (with Hashtag) inside the ByRow but didn't work.
the formula of column C is
=LET(Brine_Name,BYROW($A$2:$A$32,LAMBDA(x,LEFT(x,MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0)-2))),Brine_Condition,LEFT($C$1,MATCH(TRUE,ISNUMBER(--MID($C$1,ROW(INDIRECT("1:"&LEN($C$1))),1)),0)-2),filtering_Brines,FILTER($A$2:$A$32,Brine_Name=Brine_Condition),filtering_Brines)
the formula in column D for extracting the numbers from the filtering brines variable resulting from the above formula is
=LET(Brine_Name,BYROW($A$2:$A$32,LAMBDA(x,LEFT(x,MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0)-2))),Brine_Condition,LEFT($C$1,MATCH(TRUE,ISNUMBER(--MID($C$1,ROW(INDIRECT("1:"&LEN($C$1))),1)),0)-2),filtering_Brines,FILTER($A$2:$A$32,Brine_Name=Brine_Condition),Extract_Numbers,BYROW(filtering_Brines,LAMBDA(x,TRIM(MID(x,MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0),SEARCH("ppg",x)-MATCH(TRUE,ISNUMBER(--MID(x,ROW(INDIRECT("1:"&LEN(x))),1)),0)))+0)),Extract_Numbers)
I Tried to use the XL2BB but the mini sheet options is not there.
