Skip navigation

HELP: auto-count text-string and charting

395 Views 7 Replies Latest reply: Nov 29, 2012 11:48 PM by Barry RSS
edoardofrombologna Calculating status...
Currently Being Moderated
Nov 27, 2012 4:57 AM

I'm very very new to spreadsheets, so apologize for dumb question.

I have a sheet containing bibliographical data from which I'd like to get charts that analyze the contents of single or multiple columns (see screenshot).

Basically columns contain text strings (authors, dates, geographical area and so on). Is it possible to auto-count occurrences of all text strings in a column and pass the results to a chart? I guess there should be a formula to do this but, as I'm pretty new, I'm totally unaware of formulas power.

What I'm manually doing now is, let's say, sort "author" column, count the rows for a single author and transcribe the data (author, occurrences) in a new sheet. After having done that I create the chart from the new sheet. The point is that it is a bit tedious (200+ entries) and if I have to add new data to the main sheet, I have to update data manually in the other sheets.

So the workflow, I think, should be: count all text strings and pass the data to a chart

Can you help me?iwork numbers.jpg

Numbers, OS X Mountain Lion (10.8.2)
  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Nov 27, 2012 11:04 AM (in response to edoardofrombologna)

    Edoardo,

     

    You can chart the count of each item so long as you have the count.  To get the count you need a list of each unique... or distinct... (Jerry or Barry will clarify    ) item in the author (or what ever column).... I am focusing on the Author then you can expand to other columns.

     

     

    To get the list of distinct authors add a new column to your book table.

     

    Screen Shot 2012-11-27 at 12.59.10 PM.png

     

    I called the column "Distinct Author".  This column lists each author only once.

     

    B2=IF(IFERROR(MATCH(A2, $A$1:A1, 0), 0)>0, "",A2)

    select B2 then fill down as needed

     

    the select column B (except for the header row), copy.

     

    Now create a new table and give it the title "Author Summary":

    Screen Shot 2012-11-27 at 1.01.12 PM.png

     

    Make the first row and the first column a header

    now select cell A2, then paste

     

    now sort column A by author as needed

     

    now enter the formula:

    B2=COUNTIF(Book List :: A, "="&A2)

     

    select B2 and fill down as needed

     

     

     

    to make a pie chart select the cells in column B (the "Count" column), then select the pie chart from the "Chart" menu in the tool bar

  • Wayne Contello Level 6 Level 6 (12,670 points)
    Currently Being Moderated
    Nov 27, 2012 1:48 PM (in response to edoardofrombologna)

    try using the following:

     

    =IF(IFERROR(MATCH(A2; $A$1:A1; 0); 0)>0; "";A2)

     

    i.e. change "," to ";"

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 28, 2012 9:17 PM (in response to edoardofrombologna)

    Buongiorno Edoardo,

     

    You worte:

    "I'm saying it for non-English speakers and it's quite annoying as 99% of help you can get is in English meaning a non-English speaker will have to 'localize' all 'commands' which in some cases in not so self-evident..."

     

    Most, but not all of the responders to questions in this user-to-user community have English as their native tongue. Their copies of Numbers, which they use to test their answers, is localized to one of the English speaking regions, so they will not be familiar with other localizations of Numbers and the function names for the functions it supports.

     

    If your question is in English, the assumption of those responders is often that your copy of Numbers is also loclised to an English language region, and that you are expecting an answer in English, and appropriate to one of those regions.

     

    If your question is in a language other than English, many of the responders, Wayne included, will turn to Google Translate (or other similar services) to translate the question into English and the answer back into the original language in which the question was posted. Computer generated translations from online services are seldom 100% accurate, so these will generally be accompanied by a note that the response was translated, and naming the service used.

     

    I'm not sure that you can expect better 'service' than that.

     

    Google translation follows:

    Traduzione di Google segue:

     

    La maggior parte, ma non tutti i soccorritori di domande di questo user-to-user comunità hanno l'inglese come lingua madre. Le loro copie di numeri, che vengono utilizzate per testare le loro risposte, è localizzato in una delle regioni di lingua inglese, in modo da non avere familiarità con localizzazioni altri numeri ed i nomi delle funzioni per le funzioni che supporta.

     

     

    Se la tua domanda è in inglese, l'assunzione di tali risponditori è spesso che la copia di numeri è anche loclised ad una regione di lingua inglese, e che ci si aspetta una risposta in inglese, e di conferire ad uno di tali regioni.

     

     

    Se la tua domanda è in una lingua diversa dall'inglese, molti dei soccorritori, Wayne incluso, si rivolgono a Google Translate (o altri servizi simili) per tradurre la domanda in inglese e la risposta di ritorno nella lingua originale in cui è stato postato la domanda . Le traduzioni automatiche generate dai servizi online raramente sono precisi al 100%, per cui questi saranno generalmente accompagnate da una nota che la risposta è stato tradotto, e la denominazione del servizio utilizzato.

     

     

    Io non sono sicuro che ci si può aspettare una migliore 'servizio' di questo.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 29, 2012 11:48 PM (in response to edoardofrombologna)

    HI Edoardo,

     

    Thanks for the clarification. Those are legitimate observations.If you want them to get to Apple, I'd suggest using the feedback channel. There's a link to it in the Numbers menu. In English versions, it's named Provide Numbers Feedback.

     

    Picture 24.png

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.