Spreadsheet Karma

=QUERY({C:C,C:C},”select Col1, count(Col2) where Col1 <> ” group by Col1″,1)

This is just so handy for getting all the unique values from a column and spitting them out with the counts of their occurrences.1

I can’t tell you how many times I’ve done this a much harder way.

I saw the function above as an answer after I’d responded to a Stack Overflow question about getting unique word counts.

Just another little example of how doing things in the open and being involved in communities ends up benefitting you in unexpected ways.


flickr photo shared by New York Public Library with no copyright restriction (Flickr Commons)


1 If you data isn’t in column C, just change it to the right letter.

Comments on this post

  1. John Johnston said on October 22, 2016 at 2:10 pm

    In the sprit of throwing another way into the pot, sort of related thing, I use Martin Hawskey’s Tag’s explorer for a few tags, this bash one-liner:
    curl “https://docs.google.com/spreadsheets/d/1GFkFAlLhX3aVIkspSTXD3oSuYYqEigkMAiCTLFv-Fq0/export?format=tsv&gid=400689247” | cut -f2 | sort | uniq -c | sort -bnr

    Downloads the second sheet as a TSV, grabs the second column, sorts it, and spits out a sorted and counted list of unique twitter handles (column 2)
    Of course I need to keep the command somewhere other than in the spreadsheet, because I can never remember it.

    • Tom Woodward said on October 24, 2016 at 2:15 pm

      That will be handy. I’ve been contemplating what it’d take to auto build lists off that via the Twitter API.

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL