Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've made a survey in Sharepoint and want to convert the results into something useful in Excel 2003. My problem is the format the responses are in for some questions.

An example of two responses (=two rows) for one question (=one column) can look like this

alternative A;#alternative B;#unique freetext alternative
alternative A;#alternative C

So all alternatives the user selected are in the same column, separated by ;#

What I would like to do is to convert this to

alternative A = 2
alternative B = 1
alternative c = 1
unique freetext alternative = 1

This way it'll be easy to convert to a graph.

How would I go about doing this with the least amount of manual job? There are about 60-70 rows and each row has about 15 - 20 columns with this problem.

I'd guess I would need to somehow count the alternatives, separated by ;#?

If all else fails, I guess I could handle this with a bunch of IF statements

share|improve this question

3 Answers 3

up vote 1 down vote accepted

This isn't the prettiest or seemingly most elegant, but it's simple and will work great:

  1. Put all your results in a column.
  2. Put all your possible answers at the top of columns next to your results.
  3. Use a simple formula to search the results column for the value in the column header. (I used SEARCH() to perform case-insensitive searching per your question. You can also use FIND() if you want case-sensitive results.
  4. Include IFERROR()1 in case the value is not found.
  5. Add a totals row at the bottom.

For a one-off grunt search, this is a quick and easy way to get meaningful numbers from the concatenated fields.

Screenshot


1 Excel 2003 does not have the IFERROR worksheet function. A workaround is to use IF(NOT(ISERROR(...))). In this case, the example formula from the screenshot should read =IF(NOT(ISERROR(SEARCH(B$1,$A2)>0)),1,0)

share|improve this answer
    
This ended up being the best option but I had to gather the freetext answers manually. One addition though, the formula doesnt work on Excel 2003 since IFERROR doesnt exists. I had to do: IF(NOT(ISERROR(SEARCH(B$1,$A2)>0)),1,0) –  Fredrik Mar 1 '12 at 15:45
    
@Fredrik Oops, I didn't realize that. Glad you found a workaround and thanks for the heads-up. –  Farray Mar 1 '12 at 17:18

The technique I'd use is to count compare the lengths of all the cells in the column against the lengths of all the cells with the specific response removed, and then divide that difference by the length of the specific response. So, you'd list each possible response for a question at the top or bottom of that column, and insert a column to contain the counts. Then in the empty cells next to the possible alternatives put a formula like this:

=(SUM(LEN($B$6:$B$70))-SUM(LEN(SUBSTITUTE($B$6:$B$70,B1,""))))/LEN(B1)

This must be entered as an array formula, with Ctrl - Shift - Enter

This assumes your responses are in B6:B70. This is case-sensitive, so it wouldn't pick up your lower-case c's in your example criteria.

enter image description here

share|improve this answer

You can use text to columns with the custom delimiter ;#. (Note: you may need to identify the delimiters separately and tell excel to treat consecutive delimiters as one) This will break the text out across a row.

Then you can cut and paste special - transpose The only downfall of this procedure is you will need to insert cells between the rows when you want to paste special - transpose because you can't insert cells that way. You could transpose them in a different way and then cut - insert cells to make it the way you want it.

share|improve this answer
1  
Text to Columns only allows single-character delimiters. –  Farray Feb 20 '12 at 15:50
1  
@Farray Good point. You can identify both and tell excel to "treat consecutive delimiters as one". –  Raystafarian Feb 20 '12 at 15:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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