So I used a Google form to gather data and now need to provide interpretation. Some data was peoples views based on what event they attended. I want to create lists based on each event – ie comment 1, 3 and 5 for event A and then comments 2, 4 and 6 for event B etc.

Does anyone know the excel (or more usefully) the sheets function to gather this information cleanly…

Original data looked like:

Event
Event A – Comment 1
Event B – Comment 2
Event A – Comment 3
Event B – Comment 4
Event A – Comment 5
Event B – Comment 6

How I want it to look like

Event A
Comment 1
Comment 2
Comment 5

Event B
Comment 3
Comment 4
Comment 6

Is the Event/Comment thing really a single string per cell i.e. “Event A – Comment 5″ rather than ‘Event A” and “Comment 5”?

If it is I would just sort it alphabetically and then insert some extra lines if it is a one off and there aren’t many events.  If you are doing it more often then something a bit more complicated might be needed

If the whole string is in one cell, you can use the “left” function to generate another string with which to sort your data.

Eg in b2: (IIRC)
=left(a2,7)

This puts the first 7 characters of a2 into b2.

Text to columns function using the dash as a delimiter.
Then a simple pivot table.

Assuming the events vary in length

And that you want it to keep going as you add more data.

=LEFT(A1,(FIND(“-“,A1,1)-1))

Plus the equivalent right in two separate columns will split the data

Then as above the mystical pivot table.

Alternatively you can set it up manually with an ace formula i found but i eill need to fond it again.