5

I have a Google Spreadsheet with a single column that holds string values (Twitter screen names) such as "user1", "user1", "UserX", and I would like to count those values so that I can easily craft a bar chart out of it. So the result should be

value        occurrence
-----------------------
user1         2
UserX         1
...          ....

Please note, I only want to look for whole words, and not part words. For example, the words 'on' and 'one' appears in the word 'money' - I would not count this (i.e., only the word 'money' is counted).

Hope that is clear enough. What formula should I use?

Improve this question
7
  • Did you want to find words within words though? EG, if your spreadsheet only consisted of 1 word, which was blabla, do you want to see the results as shown in your post?
    – Dave Rook
    Dec 14, 2012 at 9:25
  • No, no words within words, maybe the example is abit confusing. "bla" and "blabla" are just two different values, could be "bla" and "blu", for example.
    – wnstnsmth
    Dec 14, 2012 at 9:29
  • And lastly, what about capitalization - does that matter. How good are you with VBa? And are all the words just 1 word, or some 2 or 3 words long (sentences)
    – Dave Rook
    Dec 14, 2012 at 9:31
  • Sorry, as per my VBa comment, is it an option to export to Excel? Otherwise, try this link developers.google.com/apps-script/overview
    – Dave Rook
    Dec 14, 2012 at 9:38
  • 1
    If you do spreadsheets often, I recommend learning a bit of scripting. Just a little can go a long way. Learning curve typically is hard at first until you get going but persevere :)
    – Dave Rook
    Dec 14, 2012 at 9:50

2 Answers 2

Reset to default
10

In Google Spreadsheet:

  1. Prepare all unique items: =UNIQUE(A1:A10)
  2. Count them: =COUNTIF($A$1:$A$10,B1)
  3. Added them together: =CONCATENATE(B1," ",C1)

See example I've prepared: Find the number of occurrence of a word

Improve this answer
0

A pivot table (under Data) should handle this requirement very well:

WA36643 example

Applying default settings the control total and alphabetical sorting are automatic.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.