Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

add order by to query formula that gets unique list and count from delimited strings in a column Google sheets

I have a formula that gets a unique list of titles from pipe-delimited string in a column and there counts

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN("|",Elements!$H2:$H),"|")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))

I need to sort the counts in a descending manor, I have tried adding order by Col2 Desc

=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN("|",Elements!$H2:$H),"|")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) order by Col2 Desc''",0))

But I get unable to parse ...

Thank you

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

use:

=INDEX(QUERY(TRANSPOSE(SPLIT(JOIN("|", Elements!H2:H), "|")&{"";""}),
 "select Col1,count(Col2) 
  group by Col1 
  order by count(Col2) desc
  label count(Col2)''", 0))

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...