0

I currently work at a college and we working to make the process of asking student clubs for information easier. We have created a google form for them to submit their member information, but each group has different amounts of members. We need the final list to be one comprehensive list of the student information, but I am not sure how to program Google Sheets to do this.

Here is what the results look like: enter image description here

Here is how we need it to look: enter image description here

I am not very proficient in programing but I am a quick Learner If someone could set me in the right direction. Here is the form:https://docs.google.com/spreadsheets/d/1b1VrE9Tgg_pHTFScKsOz6YgARxIMFn-3tec6Dae9hcs/edit?usp=sharing

I have tried using as ArrayFormula and QueryFunction, if someone could explain the correct formula using one of those or anything else, that would be a great help

Improve this question
4
  • So you have multiple members in one row? This is result from Google Form? This is not good. May be bad form design. IMHO It should come from Google Form as one record per line. Please add a link to Google Form to make it more clear.
    – Matěj Kříž
    Dec 18, 2020 at 17:26
  • It would come as one record if we made each member fill out a separate form, but we have one representative fill out one form for the whole group. here is the google form: docs.google.com/forms/d/…
    – Lysa
    Dec 18, 2020 at 23:49
  • Does this answer your question? Forms Data Manipulation In Google Sheets
    – Matěj Kříž
    Dec 19, 2020 at 18:19
  • You have been referred to a potential duplicate. This is a very similar scenario to yours. Probably each answer will work for you, but may I suggest that you adapt this answer provided by @marikamitsos. It is elegant in its simplicity, and easily adapted to your scenario (I tested it).
    – Tedinoz
    Dec 20, 2020 at 1:59

1 Answer 1

Reset to default
0

Try this formula in cell A1 of a blank sheet:

=query({Responses!B1,Responses!D1:G1;Responses!B2:B,Responses!D2:G;Responses!B2:B,Responses!H2:K;Responses!B2:B,Responses!L2:O}," select * where Col2<>'' order by Col1")

Logic

  • the query uses curly brackets ({})- this identifies an array content

  • there are four distinct ranges separated by a semi-colon (;). This means that the ranges are "stacked" one on top of the other.

    • each of those ranges consists of two ranges separated by a comma (,). This means that those ranges are "joined" on the same row

  • Range#1 = Header Row;

    • it consists of Cell B1 ("Organisation") and Cell D1:G1 - the headers for the member information

  • Range#2 = Column B2:B and the first set of member columns (D2:G)

  • Range#3 = Column B2:B and the second set of member columns (H2:K)

  • Range#4 = Column B2:B and the third set of member columns (L2:O).

  • where Col2<>''

    • Column 2 in this context is "Last Name"; so this excludes blank sets

  • order by Col1

    • displays the results sorted by "Organisation".
    • It isn't possible to put "Athletes" ahead of "Artistic" simple because of alphabetic sequence.

Credit:marikamitsos

Forms Data Manipulation In Google Sheets

Improve this answer

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