How to do google sheet filtering? How to Sort?

Question

https://docs.google.com/spreadsheets/d/e/2PACX-1vT0X3Lky90cnTWg6RLr-J8NotNbj9-LACD0U4S0j3yhBEm6hlZEajdaIJ_OIUBZcstRlMkZqk5Ccuaq/pubhtml?gid=845418160&single=true

I have a google sheet like the above link it looks like this

Timestamp   Exact   Username of Hacker  Video Link
6/30/2020 1:55:21   Column1DummyData    
6/30/2020 1:59:09   Column1DummyData    
6/30/2020 1:59:14   Column1DummyData    
6/30/2020 1:59:19   Column1DummyData    
6/30/2020 2:59:31   Svd 
6/30/2020 2:59:37   Svd 
6/30/2020 10:38:15  qwerty  
6/30/2020 10:44:15  test                https://www.youtube.com/watch?v=3UZzu4UQLcI 
6/30/2020 10:58:53  test                https://www.youtube.com/watch?v=HbgzrKJvDRw
6/30/2020 10:59:33  test                https://www.youtube.com/watch?v=gxpX_mubz2A

I managed to make an output like this using the query:

*=ARRAYFORMULA({
 QUERY(B3:B, "select B,count(B) where B !='' group by B label count(B)''"), IFNA(VLOOKUP(
 QUERY(B3:B, "select B,count(B) where B !='' group by B label count(B)''"), {
 QUERY(B3:C, "select B,count(B) where B !='' and C !='' group by B label count(B)''"), 
 REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(QUERY(
 QUERY(B3:C, "select count(B) where B !='' and C !='' group by B pivot C"), 
 "offset 1", 0)="",,QUERY(
 QUERY(B3:C, "select count(B) where B !='' and C !='' group by B pivot C"), 
 "limit 0", 1)&",")),,99^99))), ",$", )}, 3, 0))})*

OutPut

Name               NumberOfItem    Video link

Column1DummyData         4

Svd                      2

qwerty                   1

test                     3         https://www.youtube.com/watch?v=3UZzu4UQLcI, https://www.youtube.com/watch?v=HbgzrKJvDRw, https://www.youtube.com/watch?v=gxpX_mubz2A

But I need the output to be sorted in descending order based on NumberOfItem Column

Can't you just put another QUERY outside of the ARRAYFORMULA and use the order by parameter to sort it? — a-burge
Actually speaking yesterday is the day the first time in my life I am using Excel. type things. so Its a lot confusing for me. — Savad
=QUERY(ARRAYFORMULA({ QUERY(B3:B, "select B,count(B) where B !='' group by B label count(B)''"), IFNA(VLOOKUP( QUERY(B3:B, "select B,count(B) where B !='' group by B label count(B)''"), { QUERY(B3:C, "select B,count(B) where B !='' and C !='' group by B label count(B)''"), REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(QUERY( QUERY(B3:C, "select count(B) where B !='' and C !='' group by B pivot C"), "offset 1", 0)="",,QUERY( QUERY(B3:C, "select count(B) where B !='' and C !='' group by B pivot C"), "limit 0", 1)&",")),,99^99))), ",$", )}, 3, 0))}),"order by Col2 desc",-1) — a-burge

player0 player0 · Accepted Answer · 2020-06-30T22:17:43

use:

=ARRAYFORMULA(QUERY({
 QUERY(B2:B, "select B,count(B) where B !='' group by B label count(B)''"), IFNA(VLOOKUP(
 QUERY(B2:B, "select B,count(B) where B !='' group by B label count(B)''"), {
 QUERY(B2:C, "select B,count(B) where B !='' and C !='' group by B label count(B)''"),   
 REGEXREPLACE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(QUERY(
 QUERY(B2:C, "select count(B) where B !='' and C !='' group by B pivot C"),
 "offset 1", 0)="",,QUERY(
 QUERY(B2:C, "select count(B) where B !='' and C !='' group by B pivot C"),
 "limit 0", 1)&",")),,99^99))), ",$", )}, 3, 0))}, "order by Col2 desc", 0))

How to do google sheet filtering? How to Sort?

1 Answers