How to transpose selected rows of Data into multiple columns in Excel

Given a set of data in one single column, and you need to transpose every N number of rows into columns in Excel. The first task is to figure out how many rows or lines of data corresponds to one set. Once you have figured out how many consecutive rows of data corresponds to one set or a single records, then next step is transpose the selected rows into columns. 

Lets take a simple example, given a data as below in one single column.

aaa1

aaa2

aaa3

bbb1

bbb2

bbb3

ccc1

ccc2

ccc3

The first thing is to figure out how many rows correspond to one set. To make it simpler, we can see aaa1,aaa2,aaa3 belong to one set, and similarly bbb1,bbb2,bbb3 belongs to a different set. Hence the data represents three different records. The objective is to transpose the 3 lines or rows of data into separate columns, as below showing 3 distinct records.

aaa1  aaa2  aaa3

bbb1 bbb2  bbb3

ccc1  ccc2  ccc3

The data now can now be represented into a Table format with each row corresponding to one separate record.

For this transpose of rows into column use the Excel formula of INDEX. Since there are 3 rows of data which needs to be transposed (as in the above example), the formula will be =INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))

$A corresponds to the Excel spreadsheet where the data is held. 

See the video below, which shows how the data available under Column A, has city information has been transposed into multiple columns. The video shows City data in multiple rows, which is nicely transposed into columns giving it a Tabular format for easy analysis.

Transpose data from Rows to Columns

The video shows the example where City data is available in a single column. Each of 7 rows correspond to a city, so 7 rows correspond to one unique record. The task is to transpose 7 rows of data into column to give a Tabular format showing City Data as below

City Data

Since there are 7 rows that need to be transposed, the formula will now be =INDEX($A:$A,ROW(A1)*7-7+COLUMN(A1))

Once you enter the formula in the selected cell, you will need to take another step, to drag the selected cell horizontally to cover 7 cells, since you are transposing data from 7 rows (see from 1:01 minute to 1:18 in the above video this step is shown). This will give you the first row of your unique City data, in the example it is Shanghai. (Don't worry about the City, Latitude, Country etc. that is showing in the first row- you will see in the video it was manually typed). The final step is now to drag the first row down so multiple rows will be autofilled (see from 1:23 minute to 1:47). Hope the video provides clarity. In the end there are additional rows with 0 data which you can safely delete. At 1:53 the Table headers are manually created. If you need the original City data text file used in the example, let me know and will post it.

Here is a reference that I followed, which has examples as well of similar problem.