英文:
How to change the TextJoin range in google sheet dynamically based on empty cell
问题
我想要连接文本，但每个记录的行数不同。如果我使用静态范围，对于一些记录，数据会丢失，在下面的例子中，22Inch就缺失了。
使用了下面的公式。如何动态更改范围。
=TEXTJOIN(",",TRUE,A3:A8,"")
英文:
I want to join the text but the number of rows differ for each record. If I use the static range data is missing for few records, in below example 22Inch is missing,
Used the below formula. How to dynamically change the range.
=TEXTJOIN(",",TRUE,A3:A8,"")
答案1
得分: 3
尝试这个：
=TEXTJOIN("，", 1, A3:INDEX(A:A, MIN(IFERROR(1/(1/(ROW(A3:A)*(A3:A="")))))))
英文:
Try this:
=TEXTJOIN(",", 1, A3:INDEX(A:A, MIN(IFERROR(1/(1/(ROW(A3:A)*(A3:A="")))))))
答案2
得分: 1
你能测试一下这个吗：
=LAMBDA(z,BYROW(z,LAMBDA(a,REGEXREPLACE(TEXTJOIN(";",1,IFNA(FILTER(A2:A,XLOOKUP(ROW(B2:B),z,z,,-1)=a))), "^(.*?)\;", "")) ))({ROW();BYROW(A3:A,LAMBDA(z,IF((z<>"")*(OFFSET(z,-1,0)=""),ROW(z),)))})
英文:
Can you test this one out:
=LAMBDA(z,BYROW(z,LAMBDA(a,REGEXREPLACE(TEXTJOIN(",",1,IFNA(FILTER(A2:A,XLOOKUP(ROW(B2:B),z,z,,-1)=a))),"^(.*?)\,",""))))({ROW();BYROW(A3:A,LAMBDA(z,IF((z<>"")*(OFFSET(z,-1,0)=""),ROW(z),)))})
答案3
得分: 1
这是另一个你可以尝试的公式:
`
=QUERY(INDEX(LAMBDA(ζ,SORT(REGEXREPLACE(ζ,"^,",""),COUNTIFS(A1:A,"",ROW(A1:A),"<="&ROW(A1:A)),1,LEN(ζ),""))({"";IF({A3:A;""}<>"",,SCAN(,A2:A,LAMBDA(a,c,IF(c="",,a&","&c))))})), "limit "&ROWS(A2:A))
`
[![enter image description here][1]][1]
英文:
Here's another formula you can try:
=QUERY(INDEX(LAMBDA(ζ,SORT(REGEXREPLACE(ζ,"^,",),COUNTIFS(A1:A,"",ROW(A1:A),"<="&ROW(A1:A)),1,LEN(ζ),))({"";IF({A3:A;""}<>"",,SCAN(,A2:A,LAMBDA(a,c,IF(c="",,a&","&c))))})),"limit "&ROWS(A2:A))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论