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
-
What's better: avoid unneeded information in the result or delete it afterwards? :-)Dominique– Dominique2024-10-22 14:01:49 +00:00Commented Oct 22, 2024 at 14:01
-
always avoid. never destructive act :) the solution by Chat GPT I shared with slight modifications did the trickKobe2424– Kobe24242024-10-23 13:24:30 +00:00Commented Oct 23, 2024 at 13:24
Add a comment
|
2 Answers
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:
• 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,""))
=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))),""))
answered Oct 22, 2024 at 1:20
Mayukh Bhattacharya
29.2k9 gold badges31 silver badges46 bronze badges
4 Comments
Kobe2424
What if I wanted in addition to empty cells also avoid also cells in the column "Details" that contain the substring FRUIT, please see edited question
pgSystemTester
@Kobe2424 you first thank the person who was kind enough here to answer the question you posted by
accepting the answer. Then you go post a new question in a correct method. Editing questions is uncool after people put in work. Also consider the likelihood of success going the edit route vs. accept: if you accept, and post new question, MB is likely going to inspired to help you again. Editing a post... meh.... good luck.Kobe2424
@pgSystemTester You're right I didn't mean to be rude, I'll do that.
pgSystemTester
@Kobe2424 good job. I worked with Kobe Bryant a couple times at UCI track. #Facts.
I found an answer with the help of Chat GPT and some modifications I did, might help someone else, here it is:
=IF(AND([@Details]<>"", LEFT([@Details], 6)<>"FRUIT:"),
IF(OR(INDIRECT(ADDRESS(ROW()-1,COLUMN([Details])))="", LEFT(INDIRECT(ADDRESS(ROW()-1,COLUMN([Details]))), 6)="FRUIT:"),
TEXTJOIN(CHAR(10), TRUE, [@Details]:INDEX([@Details]:INDIRECT(ADDRESS(50000,COLUMN([Details]))), MATCH(1, (INDIRECT(ADDRESS(ROW()+1,COLUMN([Details]))):INDIRECT(ADDRESS(50000,COLUMN([Details])))="")+(LEFT(INDIRECT(ADDRESS(ROW()+1,COLUMN([Details]))):INDIRECT(ADDRESS(50000,COLUMN([Details]))), 6)="FRUIT:"), 0))),
""),
"")