0

I have a Google Form response sheet, part of which contains several columns (say B:G) that will only have a single value between them. The form asks respondents for their county of residence and then their town of residence within that county on separate pages as a means to get around the fact that Forms doesn't have support for dependent questions.

Regardless, I'm trying to figure out a formula to return the value contained in these columns for each row, as well as a way to autopopulate this when new form responses are submitted. I gather this might include the use of ArrayFormula, but I haven't been able to figure it out.

Any advice?

Here's a sample sheet with dummy form data and the ideal return.

https://docs.google.com/spreadsheets/d/1EHcjuoNd1wQLbD7ceb1i6lsxWs0iU4w-Ermz095Qmbg/edit

Improve this question
3
  • 2
    Please don't use URL shorteners here. They're all-too-often used to hide spam or malware. Use the full URL.
    – ale
    Apr 13, 2015 at 0:22
  • I will echo Al's sentiment. Please expand the URL.
    – jonsca
    Apr 13, 2015 at 5:31
  • Apologies. Newbie. Fixed.
    – sstirling
    Apr 13, 2015 at 21:45

1 Answer 1

Reset to default
2

Try:

=ARRAYFORMULA(transpose(split(concatenate(if(B2:D<>"", B2:D,)&char(9)),char(9))))

or:

=ARRAYFORMULA(transpose(split(concatenate(B2:D&char(9)),char(9))))

or also:

=ArrayFormula(transpose(trim(query(transpose(B2:D),,rows(A:A)))))
Improve this answer
1
  • 1
    This is why the internet is a wonderful place. #3 worked perfectly. Thanks JPV!
    – sstirling
    Apr 12, 2015 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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