Forum Discussion
Table of Data: Is Countifs my best solution?
Hi,
I have a very large table showing models and PNs. A model may have multiple PNs. What I need to have is a concise list of all model numbers with the PNs they have in the same row. For example:
Model A: PN1
Model A: PN2
Model B: PN1
Model B: PN3
What I need is:
Model A: PN1, PN2
Model B: PN1, PN3
After I get the data in that format, I need to say, what models have PN1 and PN3?
I have several hundreds of thousands of rows, so the countfs are really slow with calculating. Thanks!
Skip the filter_array argument with a comma or stop the formula at:
=GROUPBY(DemoTbl[Model],DemoTbl[PN],ARRAYTOTEXT,,0)
With worksheet functions, GROUPBY is your best bet. The speed of calculation will likely depend on the strength of your filter.
For example:
=GROUPBY(DemoTbl[Model],DemoTbl[PN],ARRAYTOTEXT,,0,,DemoTbl[Model]<>"F")This works great. What do I do if I dont want to exclude a model? =GROUPBY(DemoTbl[Model],DemoTbl[PN],ARRAYTOTEXT,,0,,)?
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);
GROUPBY Function did not work in my Excel. So I had to settle for something more classical. Here's what I Did:
Model SL (Shortlist) =SORT(UNIQUE(DataTable[Model]))
All PNs =TEXTJOIN("; ",TRUE,FILTER(DataTable[PN],DataTable[Model]=E3))
