英文:
Repat the rows n times when column data is present
问题
我有一个如下所示的输入
期望的输出是
A到G列的数据需要在行中重复n次，其中n是范围H到Q中项目的计数
例如，第5行A4 B4 H6 D4 E4 F4 G4重复5次，因为H5到Q5列有5个项目。
我尝试过了
=ArrayFormula(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY({A1:G10,TRANSPOSE(SPLIT(REPT(CONCATENATE(H1:Q1&","),COUNTA(H1:Q1)),"","))},"Select * ")),",")))
英文:
I have an input as shown here
The expected output is
The A to G column data needs to be repeated in the rows n times
where n is the count of items present in the range H to Q
For example the 5th row A4 B4 H6 D4 E4 F4 G4 is repeated 5 times since H5 to Q5 column has 5 entries for the 5th row
I have tried with
=ArrayFormula(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY({A1:G10,TRANSPOSE(SPLIT(REPT(CONCATENATE(H1:Q1&","),COUNTA(H1:Q1)),","))},"Select * ")),","))))
答案1
得分: 1
以下是代码的翻译部分：
=index(let(Σ,{"";"";"";"";"";"";""},Ξ,H2:index(Q:Q,match(2,1/(byrow(A1:G14,lambda(Σ,textjoin("",1,Σ))<>""))),Δ,byrow(Ξ,lambda(Σ,max(1,counta(Σ)))),Γ,byrow(Ξ,lambda(Σ,if(counta(Σ)=0,"🀠",Σ))),
{query(reduce(Σ,sequence(counta(A2:A14)),lambda(a,c,{a;if(sequence(index(Δ,c)),index(A2:G14,c))}),"offset 1"),
substitute(tocol(Γ,1),"🀠","")}))
OR
=index(let(Σ,match(2,1/(byrow(A1:G14,lambda(Σ,textjoin("",1,Σ))<>"")),
{iferror(split(tocol(map(byrow(A2:index(G:G,Σ),lambda(Σ,textjoin("|",1,Σ))),byrow(H2:index(Q:Q,Σ),lambda(Σ,max(1,counta(Σ)))),lambda(a,b,wraprows(a,b,a))),1),"|")),
substitute(tocol(byrow(H2:index(Q:Q,Σ),lambda(Σ,if(counta(Σ)=0,"🀠",Σ)),1),"🀠","")})))
希望这有帮助。如果您有其他问题，请随时提问。
英文:
You may try:
=index(let(Σ,{"","","","","","",""},Ξ,H2:index(Q:Q,match(2,1/(byrow(A1:G14,lambda(Σ,textjoin("",1,Σ)))<>""))),Δ,byrow(Ξ,lambda(Σ,max(1,counta(Σ)))),Γ,byrow(Ξ,lambda(Σ,if(counta(Σ)=0,"🐠",Σ))),
{query(reduce(Σ,sequence(counta(A2:A14)),lambda(a,c,{a;if(sequence(index(Δ,c)),index(A2:G14,c))})),"offset 1"),
substitute(tocol(Γ,1),"🐠","")}))
OR
=index(let(Σ,match(2,1/(byrow(A1:G14,lambda(Σ,textjoin("",1,Σ)))<>"")),
{iferror(split(tocol(map(byrow(A2:index(G:G,Σ),lambda(Σ,textjoin("|",1,Σ))),byrow(H2:index(Q:Q,Σ),lambda(Σ,max(1,counta(Σ)))),lambda(a,b,wraprows(a,b,a))),1),"|")),
substitute(tocol(byrow(H2:index(Q:Q,Σ),lambda(Σ,if(counta(Σ)=0,"🐠",Σ))),1),"🐠","")}))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论