Making Charts in excel with data from jot-form that has multiple results per cell

  • Profile Image
    bar12
    Asked on January 23, 2012 at 02:02 PM

    So I have results from a survey I made and am trying to make a table of the total number of responses. The issue is that the question allowed the user to select multiple items so all of the responses are on mutliple lines in one cell. This would be all well and good except that I cant figure out how to have excel see each answer as a seperate item and not just a long combination of all the selected responses per user...ill attached a screen shot. Any data or survey I would make puts each cells options combined as a list...any help in how to fix this or change excel to see results per line would be awesome!

    Ps the survey is about healthcare coverage for healthcare stuff if youre wondering =P

     

  • Profile Image
    fxr
    Answered on January 24, 2012 at 08:30 AM

    I don't have access to Excel to test this all out quickly, but I have had one of colleagues test it out for me, so the formulas should work. 

    Generate your table on the right of your spreadsheet a bit like this:

    AY           |-| AZ 

    Children    =COUNTIF(B:B,"*" & "Children" & "*") 

    That should count the number of occurences of the 'Children' text in each of the cells on that B row. 

    then:

    Adolescents =COUNTIF(B:B,"*" & "Adolescents" & "*") 

     

    Continue to build up your table for each of the required options/text.

    That should allow you to draw out the data you want from those cells. 

     

    Do you think that will help? Can you try it out please? Let us know if there is anything you are unsure of with that.