Thursday, December 27, 2012

yogi_Change Multiple Sets Of Data In Column A To Multiple Rows Beginning With Row 1 Skipping Unneeded Rows


                                          Google Spreadsheet   Post  #944
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 27, 2012
user hshaikh said:(http://productforums.google.com/forum/?zx=7e91x0d3naeu#!category-topic/docs/spreadsheets/xIfao0gKDEc)
Google spreadsheet: how to change multiple sets of data in one column to multiple rows

I have 5 rows of data in one column on google spreadsheets that I would like to be displayed in one row.  I have many sets of data like this which are separated by 3 rows.  I want each data set to be displayed across a row (5 columns).   Is there a formula I can use in order to do that over multiple data sets?  I tried transpose function but was unable to loop it for other data sets.

for example, I have one column as follows:

Name
Description
location
rating
review
- 1st row (blank)
- 2nd row (blank)
- 3rd row (blank)
Name2
Description2
location2
rating2
review2
- 1st row (blank)
- 2nd row (blank)
- 3rd row (blank)
Name3
...etc.

This is how I would like this data to be displayed (multiple rows with 5 columns of data):
name description location rating review
name2 description2 location2 rating2 review2
...etc for multiple sets of data

Would love any input as I have been working on this for hours and can't figure it out.

Thanks!
-------------------------------------------------------------------------------------------------------
following is a solution to the problem