Forum Discussion

Wee001's avatar
Wee001
Copper Contributor
Oct 29, 2023

Question on Merging of rows

Hi, I have a sheet of over 5000 rows, and I'm looking to merge rows with a similar name, while combining the rows so that main row contains all the necessary information. Not really sure how to expla...
excel
Formulas and Functions
peiyezhu's avatar
peiyezhu
Bronze Contributor
Oct 30, 2023

 

=webservice("https://e.anyoupin.cn/EData/?v=111&q=cli_tb~sht~"&a17&b19

Wee001 

 

a17=

ARRAYTOTEXT(A2:G9,1)

a19=

 

=webservice("https://e.anyoupin.cn/EData/?v=111&q=cli_tb~sht~"&a17&select f01,f02,colIdxf[2:]{iif(cast(group_concat(%s,'') as int)=1,"TRUE","FALSE")} from sht group by f01,f02

 

a21=TEXTSPLIT(A19,"□","△")

 

 

view-source:https://e.anyoupin.cn/EData/?v=111&q=cli_tb~sht~{%22Ben%22,%22Lee%22,TRUE,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22;%22Ben%22,%22Lee%22,%22%C2%A0%22,TRUE,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22;%22Ben%22,%22Lee%22,%22%C2%A0%22,%22%C2%A0%22,TRUE,%22%C2%A0%22,%22%C2%A0%22;%22Jon%22,%22Wee%22,%22%C2%A0%22,TRUE,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22;%22Jon%22,%22Wee%22,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22,TRUE,%22%C2%A0%22;%22Roy%22,%22Ho%22,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22,TRUE;%22Roy%22,%22Ho%22,%22%C2%A0%22,%22%C2%A0%22,TRUE,%22%C2%A0%22,%22%C2%A0%22;%22Roy%22,%22Ho%22,TRUE,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22,%22%C2%A0%22};select%20f01,f02,colIdxf[2:]{iif(cast(group_concat(%s,%27%27)%20as%20int)=1,%22TRUE%22,%22FALSE%22)}%20from%20sht%20group%20by%20f01,f02

peiyezhu's avatar
peiyezhu
Bronze Contributor
Oct 30, 2023
a19=
webservice("

cli_tb~sht~{"Ben","Lee",TRUE," "," "," "," ";"Ben","Lee"," ",TRUE," "," "," ";"Ben","Lee"," "," ",TRUE," "," ";"Jon","Wee"," ",TRUE," "," "," ";"Jon","Wee"," "," "," ",TRUE," ";"Roy","Ho"," "," "," "," ",TRUE;"Roy","Ho"," "," ",TRUE," "," ";"Roy","Ho",TRUE," "," "," "," "}
;
select f01,f02,colIdxf[2:]{group_concat(%s,'-')} from sht group by f01,f02;
select f01,f02,colIdxf[2:]{iif(instr(group_concat(%s,'-'),'1'),'TRUE','FALSE')} from sht group by f01,f02")

Resources