Coding for a question with multiple answers in Excel

[achusp]

Hi,

A thanks in advance to whoever replies

I have a question in my project for which participants can select multiple answers. The data was obtained through google forms where the file was exported to csv

for example for a question it may have answers like following

Respondent 1 - 01;02;03;05;07;12
Respondent 2 - 01;02;03;04;07;08
Respondent 3 - 01;03;06;07;09;10

There are 12 options in actual …for convenience sake I’m putting the options as alphabets and they are seperated with semi colons like shown in the pic. How can I automatically split the values such that there is a column for each option with values 0 or 1 against the cell
Like shown in the below pic

 

[Toadstool]

Hi Achusp,

Does this do what you want?

Achusp.xlsx
	A	B	C	D	E	F
1	Respondents	Question
2	Respondent 1	A;C;E
3	Respondent 2	A;B;C
4	Respondent 3	A;C;D
5
6
7	Respondent	Question 1
8		A	B	C	D	E
9	Respondent 1	1	0	1	0	1
10	Respondent 2	1	1	1	0	0
11	Respondent 3	1	0	1	1	0
Sheet1

Cell Formulas
Range	Formula
B9:F11	B9	=IF(ISNUMBER(FIND(B$8,INDEX($B$2:$B$4,MATCH($A9,$A$2:$A$4,0)))),1,0)

 

[achusp]

Oh definitely !!! You are a life saver !! Thanks a million !!! But the thing is the options in actual are long phrases and there are 12 answer options + an "other" option as such too. If you could, could you please tell me more about how you did this formula? I don't know much about excel commands per se

 

[achusp]

Never mind, I just replaced the options with A,B,C,D...etc and did it with your formula. Thanks a bunch mate !!!!

 

[Toadstool]

I don't use Google Forms so I don't know the format of the data you've exported. It would be most helpful if you could use XL2BB to provide a sanitized example of your data and desired results so forum members can better assist.

Until I can see a real example I can only explain the formula provided.

The formula in cell B9 is constructed in such a way (through use or absence of a "$" to indicate absolute or relative addressing) that it can be copy & pasted down and left to right but maintains the correct cell addressing. The formula in B9 is
=IF(ISNUMBER(FIND(B$8,INDEX($B$2:$B$4,MATCH($A9,$A$2:$A$4,0)))),1,0)
but I will explain using the formula in D10 so you understand the use of the "$".
=IF(ISNUMBER(FIND(D$8,INDEX($B$2:$B$4,MATCH($A10,$A$2:$A$4,0)))),1,0)

As with math the calculations/functions are performed from the innermost parentheses outwards. Breaking down that formula from the innermost outwards gives:

MATCH($A10,$A$2:$A$4,0)
MATCH returns the row or column number which matches. In this case we are searching for $A10 ($A says the column is fixed and 10 says the row is relative when you copy&paste the formula) which is "Respondent 2" and we're searching $A$2:$A$4 so it returns a row. In this case 2.

INDEX($B$2:$B$4, )
INDEX retrieves a cell contents for the specified row and/or column. In this case we only specify a row (the 2 returned by the MATCH) so our INDEX results are "A;B;C", the second row of $B$2:$B$4.

FIND(D$8, )
This looks for the string in $D8, the heading contents "C", in the string returned by INDEX, "A;B;C". If it find it then it returns a number which is the position of the match (in this case we would get a 5 because "C" is in the fifth position of "A;B;C". If the string searched for isn't found the you get a #VALUE error.

ISNUMBER( )
Gives a logical TRUE if the result of the FIND was a number, in this case it returns a TRUE as 5 is a number. If the FIND had returned a #VALUE error then ISNUMBER would return a FALSE.

=IF( ,1,0)
IF checks a logical state and returns the first option if TRUE or the second if FALSE. In this case ISNUMBER returned a TRUE so it gives the first option of 1. If the check was FALSE then it would return the second option 0.

 

[achusp]

Man, truly !!! Thank you Thank you Thank you !!! You saved me three days worth of manual editing !!!!

 

[Toadstool]

You're welcome!
...but please, not so effusive. You're making me blush.

 

[achusp]

Haha, thats cuz you don't know how many hairs I've pulled out over this !!!! :D :D