excelstringexcel-formulaexcel-tablestextjoin

Excel TEXTJOIN of non empty rows in a table

I have a table named "Table1" and a column named "Details". I would like help with joining the text in column Details into one cell with a linebreak separator - CHAR(10), I would also like to avoid empty rows and rows that contain the substring "FRUIT" and place the value on the first row that is not empty in each "session". I shared an image to better understand enter image description here

Solution

  • Here is one way of achieving the desired output. However, it is not as elegant as it could be, but it does the job as expected:

    enter image description here

    • Formula used in cell B2

    =LET(
     a, SCAN(0,[Details]="",LAMBDA(x,y,IF(y,x+1,x))),
     b, MAP(a, LAMBDA(x, TEXTJOIN(CHAR(10),1,FILTER([Details],x=a,"")))),
     d, XLOOKUP(COUNTIF(Table12[[#Headers],[Details]]:[@Details],""),a,b,""),
     IF(IFNA(TEXTBEFORE(d,CHAR(10)),d)=[@Details],d,""))

    enter image description here

    =LET(
     _a,SCAN(,TableK[Details],LAMBDA(x,y,IF(ISERR(FIND(":",y)),x,y))),
     REDUCE("Result",UNIQUE(_a),LAMBDA(u,v,
     LET(z,FILTER(TableK[Details],_a=v),VSTACK(u,EXPAND(VSTACK("",
     TEXTJOIN(CHAR(10),,DROP(z,1))),ROWS(z),1,""))))))

    • For Structured References:

    =LET(
     a, [Details],
     b, SCAN(,a,LAMBDA(x,y,IF(ISERR(FIND(":",y)),x,y))),
     c, TOCOL(ROW(a)/FIND(":",a)^0,3),
     IFNA(IF(MATCH(ROW([@Details]),c+1,),TEXTJOIN(CHAR(10),,
     DROP(FILTER(a,LOOKUP(ROW([@Details]),c,UNIQUE(b))=b),1))),""))