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
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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment