Thanks for the useful video. One thing I'm missing is how do you deal with the "other" field like you show at 3:50. Is there a way to make a column that shows a 1 if the answer contains a custom text answer? Ideally I'd also like to make a column that contains the text of the answer as well.
Thanks Raphaël. That's a tough one. Specifically because Google Forms do not give the custom answer appended by any specific string such as "Other -" . Hence the formula to retrieve a custom answer would be complex - but possible anyway. First you'd need to check which of the given options were marked, then compare their combined lengths and the number of expected commas+spaces with the total length of the response column. If they don't match then search for the unmatched length and use the RIGHT function to obtain the custom text from the response column. Google Forms has an Apps Scripts feature which can be used to get this function working within the form itself - it will have similar logic. So you could explore that. Or you could wait and pray that Google rolls out this much-much needed feature as a simple setting. :P
@@kaisynlearning Thanks for the detailed answer! I ended up writing my own App Script for exporting the form in the format I needed. I took a while and the export isn't as fast as Google's native one but it worked for my purpose.
The answers box was empty after a new form was submitted, i need to manually highlight and drag again the box from C1, B2 to the new submitted form. Help me. Thank you
Yes there is! I just tried it in Google Sheets and the formula is exactly the same. Instead of 1 as the output value, I used $B2&" "&$C2 for the final part of the formula because my B column was first name and my C column was last name. That way, I could easily see the names of who had signed up for each column without scrolling back to the left.
This video is excellent and I have used it successfully for one survey. However, I now have one that received different surveys based on early input so now I would end up with multiple columns for the same answer. (i.e. a manager and staff member received different versions of the same registration but some events overlap,) How do combine these answers? I'm guess the first time it appears I would add a way to search if true in this column OR this column but what would the formula be?
Hi @valerieking6976 .. My apologies for getting back to you late and I hope you found your solution....you could first copy-paste-transpose your choices as usual and then place the similar choice columns next to each other by simply cutting and inserting those columns... Next, just add a final column for all such combinations where you simply check if the rows are non-zero and and place a 1 for that final field. If you'd like a detailed solution you can reach out to kaisyn.consulting@gmail.com. Thanks!
@@valerieking5575 Hope you're having a good day. I did read the comment but maybe I interpreted it differently. "I now have one that received different surveys based on early input so now I would end up with multiple columns for the same answer (i.e. a manager and staff member received different versions of the same registration but some events overlap,)" - Here I assumed you have the same question in the different versions but varying choices (and hence multiple columns for the same answer). Now looking at your question differently you may mean having different questions. So I will try giving solutions for both. Same Question, different choices in the versions - In case you have the exact same column repeated then the original formula will take the first instance and return you the answer so one single column will suffice, provided the choices given are the same in all versions. In case the choices are worded differently or have spelling variations you can combine the similar columns. Different questions, same choices in the versions - Now in case you have different questions altogether in the different versions then my solution of combining field columns still stands - you just need to first combine the answers to the two questions into a single column first. If you don't want to do this and if you would like to have a formula that evaluates both questions together you could use the IF-OR combination function like this: if(or(iserr(search(text_to_find, cell1)),iserr(search(text_to_find, cell2))),"",1)
Hello Thanks for the tutoriel.. .. i have a problem.. i enter my data with cs pro i have make a checkboxfield so that reach participant can take more Thank one option….but the way its that when i transfert the file in spss the application cant understand it, for example spss suppose to understand that 1 is to …..,2 is to .. …,3 is to … ,etc … so when a participant choose may be 1,2 and 3 …spss understand 123 and not the value of 1.. ,of 2 and of 3… so i am really lost because i have more than 1000 participants . I aspect u can propose me a solution Thank you in advance !
Hi @florianawassong9756, really sorry for the really late reply.... I hope you found your solution... SPSS does have some workarounds for splitting multiple choice columns.
Usually I just download the google form responses count for each option in the responses section of the google form itself. Is there an advantage in splitting into columns for analysis?
Thank you so much for the explanation. Very informative video. I have one problem the function did not work for me... I am trying to understand why... Please help!!
Hi, thank you for the video! I was trying to apply the same formula for my responses, but at the end it just says that I have entered too many and I cant see "1" per each answer😞
Hey, I have a query. I have the following options 1. male friends and 2. female friends. When I apply the formula though the respondent has responded as male friends and not female friends, for some responses, it is showing value 1 in the female friend's column. Is there any possible solution for that?
Hi, it's because one of the options is a part of the other (I think you mean the other way round - if the response is female friends only, then both male and female are marked as one.) In such cases you need to use an extra identifier in any one of the options - for example replace "female friends" by "female-friends" in both your data as well as your column headers.
We are trying to convert a multiple choice questions document into excel, but it's not working as expected. The questions are formatted as follows: 1. What is the name of the first man? A. James B. John C. Adam D. Prince We want to import it into excel so the question appears in a column, the same as each option. For example, Column A Column B Column C Column D Column E What is the name....? James John Adam Prince Please help us with a short video or explanation of how to achieve this. -
Hi! You could use the transpose function while pasting it in Excel (under Paste select the transpose option) but you'll need to do that for every question manually. The only other way possible is to design a macro.
@@kaisynlearning when a user fill a Google form and chooses multiple choices and that Data should be exact Under the same heading in the sheets or Excel
@teddahrable thanks, we'll try to zoom in on the formula part in our next videos. You could also try going for full screen at a higher video resolution incase the formula seems to be blurred. For now here's the formula we've shown: =IF(ISERROR(SEARCH(C$1,$B2)),"",1)
Thank You! I was scratching my head with over 300 samples with multiple "Check All That Apply" .
Video really helpful I already watched more than 4 videos but this video is perfectly for what I want for my survey.
Excellent. Very helpful. One suggestion is to paste the actual formula in your notes so folks can copy and paste it. Thanks!
Madam you're the greatest for sharing this trick
THANK YOU! I've been trying to figure this out for weeks!
This is amazing! Thank you - saves a lot of time.
Thanks it helped me a lot 😢please make more such videos
Thank you very very much... This video really save my life 🎉❤️
Hope all the good things happening to you.
Thanks from sudan🎉🎊
Thank you so much for this video. It really helps me a lot.
Thanks for the useful video. One thing I'm missing is how do you deal with the "other" field like you show at 3:50. Is there a way to make a column that shows a 1 if the answer contains a custom text answer? Ideally I'd also like to make a column that contains the text of the answer as well.
Thanks Raphaël. That's a tough one. Specifically because Google Forms do not give the custom answer appended by any specific string such as "Other -" . Hence the formula to retrieve a custom answer would be complex - but possible anyway. First you'd need to check which of the given options were marked, then compare their combined lengths and the number of expected commas+spaces with the total length of the response column. If they don't match then search for the unmatched length and use the RIGHT function to obtain the custom text from the response column. Google Forms has an Apps Scripts feature which can be used to get this function working within the form itself - it will have similar logic. So you could explore that. Or you could wait and pray that Google rolls out this much-much needed feature as a simple setting. :P
@@kaisynlearning Thanks for the detailed answer! I ended up writing my own App Script for exporting the form in the format I needed. I took a while and the export isn't as fast as Google's native one but it worked for my purpose.
I really needed this!! Thank you so much.
Boom! Just what I needed. :)
Thank you. It helped me a lot today. very grateful for you.
Can we complete this in Google Sheets? My friend did it one time but i forget how
Hi! Missed replying to you! Same formula in Google Sheets
matursuwun mbakke
you are legend! you save my life
Thank you so so much ... truly helped me on my client request.
Thank you!
The answers box was empty after a new form was submitted, i need to manually highlight and drag again the box from C1, B2 to the new submitted form. Help me. Thank you
Hi Rafael... couldn't exactly understand your query... could you send us a screenshot of your problem at kaisyn.consulting@gmail.com?
Thank you for this video!!
Great explanation. Easy to follow!
Thank you for your explanation but its not work for me and it said " you've entered too many argument in the formula" what should i do?
@shofaacademic My apologies for getting back late. I hope you found your solution. You probably entered an additional comma somewhere by mistake.
Thank you for an informative video! Is there a way to do this in google sheets (not in excel)?
Yes, the same formula holds in google sheets as well.
Yes there is! I just tried it in Google Sheets and the formula is exactly the same. Instead of 1 as the output value, I used $B2&" "&$C2 for the final part of the formula because my B column was first name and my C column was last name. That way, I could easily see the names of who had signed up for each column without scrolling back to the left.
is not workinf for me, I did all the steps but excell told me i have to put an apostrophe before =if
Check if there are any spaces in your formula
This video is excellent and I have used it successfully for one survey. However, I now have one that received different surveys based on early input so now I would end up with multiple columns for the same answer. (i.e. a manager and staff member received different versions of the same registration but some events overlap,) How do combine these answers? I'm guess the first time it appears I would add a way to search if true in this column OR this column but what would the formula be?
Hi @valerieking6976 .. My apologies for getting back to you late and I hope you found your solution....you could first copy-paste-transpose your choices as usual and then place the similar choice columns next to each other by simply cutting and inserting those columns... Next, just add a final column for all such combinations where you simply check if the rows are non-zero and and place a 1 for that final field. If you'd like a detailed solution you can reach out to kaisyn.consulting@gmail.com. Thanks!
You obviously didn't;t actually read the comment. It is actually a question you did not answer
@@valerieking5575 Hope you're having a good day. I did read the comment but maybe I interpreted it differently. "I now have one that received different surveys based on early input so now I would end up with multiple columns for the same answer (i.e. a manager and staff member received different versions of the same registration but some events overlap,)" - Here I assumed you have the same question in the different versions but varying choices (and hence multiple columns for the same answer). Now looking at your question differently you may mean having different questions. So I will try giving solutions for both.
Same Question, different choices in the versions - In case you have the exact same column repeated then the original formula will take the first instance and return you the answer so one single column will suffice, provided the choices given are the same in all versions. In case the choices are worded differently or have spelling variations you can combine the similar columns.
Different questions, same choices in the versions - Now in case you have different questions altogether in the different versions then my solution of combining field columns still stands - you just need to first combine the answers to the two questions into a single column first. If you don't want to do this and if you would like to have a formula that evaluates both questions together you could use the IF-OR combination function like this: if(or(iserr(search(text_to_find, cell1)),iserr(search(text_to_find, cell2))),"",1)
@@kaisynlearning Thank you! I will give this a try. I believe the final formula is what I'm looking for!
Hello Thanks for the tutoriel.. .. i have a problem.. i enter my data with cs pro i have make a checkboxfield so that reach participant can take more Thank one option….but the way its that when i transfert the file in spss the application cant understand it, for example spss suppose to understand that 1 is to …..,2 is to .. …,3 is to … ,etc … so when a participant choose may be 1,2 and 3 …spss understand 123 and not the value of 1.. ,of 2 and of 3… so i am really lost because i have more than 1000 participants .
I aspect u can propose me a solution
Thank you in advance !
Hi @florianawassong9756, really sorry for the really late reply.... I hope you found your solution... SPSS does have some workarounds for splitting multiple choice columns.
Usually I just download the google form responses count for each option in the responses section of the google form itself. Is there an advantage in splitting into columns for analysis?
Hi... the responses section gives a chart. Having the columns separate in google sheets or excel makes it easier to analyze on the go.
Thank you so much for the explanation. Very informative video. I have one problem the function did not work for me... I am trying to understand why... Please help!!
Hi! Missing replying to you, I hope the formula worked for you by now :P
You are amazing. Thank you so much!
Why I can't use the formula, I've already follow your step? :(
You could let us know where you're getting an issue and then maybe we can help :)
Same here. I am getting 1 in every cell
Amazing! How'd you show other options?
Other options can be tedious - do go through the comments for a detailed discussion on this
Thank you so much!
Mam, how to make a bar chart or pie chart out of this data
Hi! You can make a bar chart with the total counts for each column. Pie charts don't work for questions with multiple select.
Formula can't see....
You can try increasing the data quality for the video
Hi, thank you for the video! I was trying to apply the same formula for my responses, but at the end it just says that I have entered too many and I cant see "1" per each answer😞
Hi! Missed replying to you! Check if you have an extra comma somewhere in the formula
It’s not working for me, please help 😢
Could you be a little more specific?
Hey, I have a query. I have the following options 1. male friends and 2. female friends. When I apply the formula though the respondent has responded as male friends and not female friends, for some responses, it is showing value 1 in the female friend's column. Is there any possible solution for that?
Hi, it's because one of the options is a part of the other (I think you mean the other way round - if the response is female friends only, then both male and female are marked as one.) In such cases you need to use an extra identifier in any one of the options - for example replace "female friends" by "female-friends" in both your data as well as your column headers.
We are trying to convert a multiple choice questions document into excel, but it's not working as expected.
The questions are formatted as follows:
1. What is the name of the first man?
A. James
B. John
C. Adam
D. Prince
We want to import it into excel so the question appears in a column, the same as each option.
For example,
Column A Column B Column C Column D Column E
What is the name....? James John Adam Prince
Please help us with a short video or explanation of how to achieve this.
-
Hi! You could use the transpose function while pasting it in Excel (under Paste select the transpose option) but you'll need to do that for every question manually. The only other way possible is to design a macro.
Just excellent! Thank you very much!!!
how to shift the exact response under the heading
Hi Praveen! Could you clarify your question a bit?
@@kaisynlearning when a user fill a Google form and chooses multiple choices and that Data should be exact Under the same heading in the sheets or Excel
@@es_praveen5 That's what the video shows!! Do watch it completely!
It would be nice if I could read the formula. I'm a dinosaur so not good creating formulas in excel.
@teddahrable thanks, we'll try to zoom in on the formula part in our next videos. You could also try going for full screen at a higher video resolution incase the formula seems to be blurred. For now here's the formula we've shown: =IF(ISERROR(SEARCH(C$1,$B2)),"",1)
@@kaisynlearning thank you.
Thank you so much
Awesome!! Thank you.
it was so helpful. Thank you for this.
Thank you. Clear easy to understand
thank you a lot for this /cries. It does not work at first (also im using wps XD) but if you play around a bit, it does work! thank you!
SO nice. Thank you so much :)
VERY NICE.....IN EASIEST WAY.
SACHIN
docs.google.com/forms/d/e/1FAIpQLSe0vOzBP6BVh1FZl5FDCZz4gf5ZwH2BwXhCy_V57ao52SvHAw/viewform?usp=sf_link
Please fill this form and thanks in advance
Great resource
thank you so much mam
too good
Very nice
Very nice Neha ji
docs.google.com/forms/d/e/1FAIpQLSe0vOzBP6BVh1FZl5FDCZz4gf5ZwH2BwXhCy_V57ao52SvHAw/viewform?usp=sf_link
Please fill this form and thanks in advance