Google Spreadsheets-Find Top Five Values in a Column
I Have a Column in a Google Spreadsheet Where Each Cell Contains a Single Word. I Need to Find the Top Five Most Occurring Words. Does Anybody Know How I Can...
I have a column in a Google Spreadsheet where each cell contains a single word. I need to find the top five most occurring words. Does anybody know how I can achieve this?
2 Answers
=query(A:A,"select A,count(A) group by A order by count(A) desc limit 5 label A 'Word',count(A) 'Frequency' ")
Start with a 'helper' column. In an unused column to the right use this formula in the row containing the first word then fill down.
=if(countif(A$2:A2, A2)=1, countif(A$2:A, A2), "")
Now you can build your list of 'top five most occurring words' with this formula in another column.
=index(A$2:A, match(large(B$2:B, row(1:1)), B$2:B, 0))
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
