Convert Excel data

Question

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

Answer 1

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()¹ 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.

---

¹ 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)

Answer 2

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.

Answer 3

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.