Forum Discussion
Table of Data: Is Countifs my best solution?
Skip the filter_array argument with a comma or stop the formula at:
=GROUPBY(DemoTbl[Model],DemoTbl[PN],ARRAYTOTEXT,,0)
have several hundreds of thousands of rows, so the countfs are really slow with calculating.
If too many rows cause slow,you can import all datas to sqlite database.
Then run sql:select model,count(pn),group_concat(pn) from sheet1 group by model
https://www.sqlitetutorial.net/sqlite-group_concat/
what models have PN1 and PN3?
select * from Sheet1 limit 20;
create temp table aa as
select regexp2('^(.*):(.*)$',f01,1) model,regexp2('^(.*):(.*)$',f01,2) pn from Sheet1;
//select * from aa;
select model,group_concat(pn) from aa group by model;
create temp table bb as
select * from aa where regexp('PN1|3',pn);
select *,group_concat(pn) from bb group by model having(count(distinct(pn))>1);
select * from Sheet1 limit 20;
create temp table aa as
select regexp2('^(.*):\s*(.*)$',f01,1) model,regexp2('^(.*):\s*(\S*)$',f01,2) pn from Sheet1;
//select *,instr('PN1,PN3',pn) ,instr('PN',pn) from aa;select *,group_concat(pn) from aa where instr('PN1,PN3',pn)>0 group by model having(count(distinct(pn))>1);
Thank you. What does the 'limit 20' do? I have over 30 part numbers and close to 200k models...