Monday, January 27, 2014

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

NameItem 1Some data about item1Item 2Some data about item 2Item 3Some data about item 3Item 4Some data about item 4
FredcarVWmotorbikeBMWbicyclerustyshoesworn out
Wilmatramrelaxingboatsedate
Billtrainlatebuslatecarexpensive

into 

FredcarVW
FredmotorbikeBMW
Fredbicyclerusty
Fredshoesworn out
Wilmatramrelaxing
Wilmaboatsedate
Billtrainlate
Billbuslate
Billcarexpensive

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
--------------------------------------------------------------------------------------------------------