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

  • 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 '15 at 0:22
  • I will echo Al's sentiment. Please expand the URL. – jonsca Apr 13 '15 at 5:31
  • Apologies. Newbie. Fixed. – sstirling Apr 13 '15 at 21:45
1

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)))))
  • 1
    This is why the internet is a wonderful place. #3 worked perfectly. Thanks JPV! – sstirling Apr 12 '15 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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