List non-unique entries and their counts sorted in descending order

Question

If I have a list of names in a sheet for example:

First Name|Last Name|Something else|
Maria|Miller|...|
John|Doe|...|
Maria|Smith|...|
Marc|Meier|...|
Marc|Park|...|
Maria|Muster|...|
Selene|Mills|...|
Adam|Broker|...|

And then I want a second sheet which then shows the list of non-unique first names and their count, and the list being in descending order. So in this example that would be:

First Name|Count
Maria|3
Marc|2

What I found was this example https://infoinspired.com/google-docs/spreadsheet/sort-by-number-of-occurrences-in-google-sheets/ which sorts of partitions the sheet entries by occurrence.

So as of now I have

=UNIQUE(sort(
     Names!C3:Names!C12000;
     if(len(Names!C3:Names!C12000);countif(Names!C3:Names!C12000;Names!C3:Names!C12000););
     0;
     2;
     1
))

In the first column and

=IF(ISBLANK(A2);;COUNTIF(Names!C3:Names!C12000; A2))

In the second. This does the job somewhat (it still shows the names with count 1), but the second column needs a copying of each cell downwards for each new entry leftwards. Is there a way to tie this up directly in one line? While filtering out the unique occurrences at that. (Also the formulas are quite slow. The names sheet has about 11k entries so far. These formulas make the sheet crash at times atm. So I kind of want to sorts of comment out the formulas most of the time and only display them by commenting out the formulas. So the second column also just being one formula would be very helpful.)

score 2 · Answer 1 · answered Nov 16 '22 at 20:02

2

I think this should work if your headers are in row 1.

=QUERY(QUERY(Sheet1!A:A,"select Col1,COUNT(Col1) where Col1<>'' group by Col1",1),"where Col2>1 label Col2'Count'",1)

answered Nov 16 '22 at 20:02

MattKing

7,373
8
13

Thanks! I hope you don't mind that I accepted the other answer, despite it being a slightly later one. (As it's a shorter and for me also more readable formula.) – kumoyadori Nov 16 '22 at 20:31

player0 · Accepted Answer · 2022-11-16T20:12:41.300

2

use:

=QUERY(SORT(QUERY(A2:A, "select A,count(A) group by A"), 2, ), "where Col2>1", )

edited Nov 16 '22 at 20:12

answered Nov 16 '22 at 20:05

player0

124,011
12
67
124

ha. you should label it in the 'outer' query like me to lower your precious character count! – MattKing Nov 16 '22 at 20:08
1

Thanks, that was exactly what I needed! Nice illustrated answer, too. :D – kumoyadori Nov 16 '22 at 20:33

List non-unique entries and their counts sorted in descending order

2 Answers2