yogi_From A Table Of Names And Details Of Various Items Rearrange Data By Name And Items As In A Database List

                                         Google Spreadsheet   Post  #1501
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by Jeremy Booth (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OXn5epLkb4U)
Transposing some columns and duplicating others

Hi

I have some data collected from a google spreadsheet form. The form asks for various details but includes a name and then a couple of pieces of data about up to 4 items. In the spreadsheet this all goes in as a single row. We do various data processing exercises with this data that suits the one row per form. There is one piece that we would like to do that doesn't, and for that data I would like to transpose some data. What we would like is each item with it's data on a row with the name as the first column, so if a use fills in data for all 4 items they would have 4 rows with 3 columns, name, item and details. We would like this as a formula in the sheet so that as new forms are filled in, the data is automatically updated as it is for the other representations.

The data is sensitive so I've knocked up a test sheet here. 

We want to turn

Name	Item 1	Some data about item1	Item 2	Some data about item 2	Item 3	Some data about item 3	Item 4	Some data about item 4
Fred	car	VW	motorbike	BMW	bicycle	rusty	shoes	worn out
Wilma	tram	relaxing	boat	sedate
Bill	train	late	bus	late	car	expensive

into 

Fred	car	VW
Fred	motorbike	BMW
Fred	bicycle	rusty
Fred	shoes	worn out
Wilma	tram	relaxing
Wilma	boat	sedate
Bill	train	late
Bill	bus	late
Bill	car	expensive

I have had a search around and found functions that look useful (like transpose), but I'm no spreadsheet expert and I've not been able to think of a way to combine them to get the data we want.

Thanks

Jeremy

--------------------------------------------------------------------------------------------------------