# Excel, group a number of rows into columns

Hi I have data that has been pasted in the form of 616 rows in 1 column.

is there anyway to separate all these rows into columns knowing that every 9th row should be the beginning of a new row, is there anyway to display the data in this way?

Example: can I make cells

``````1
2
3
4
5
6
``````

into

1 2

3 4

5 6

on a larger scale

-

If the values are in A1:A616 (or any number of rows) here's a formulaic way to reorganize them. Put this formula (don't edit it) in any cell off to the right and copy it 9 columns wide, then down as far as you need to until all your data is done:

=INDEX(\$A:\$A, COLUMN(A1)+(ROW(A1)*9-9))

Notice the two 9s at the end, you can change that to any number of columns.

-
Excellent worked like a charm, Thank you very much – Likwid_T Mar 20 '12 at 20:36

Here's a manual method, for what it's worth. It won't fit your needs if you have serious data analysis to do (but since you are using Excel, that cannot be the case).

So you have a `12345` column in Column A.

Create an identical `12345` column in Column B that starts one row before (add it if necessary), and add `0` to your initial series.

You now have the following:

``````A B
0 1
1 2
2 3
3 4
5 6
etc.
``````

You now need to remove rows `01`, `23`, `45`, i.e. rows that end with an odd number.

Create a new cell equal to `=ISODD(B1)` (where `B1` designates the first cell of the second column. Fill down.

You now have the following:

``````A B C
0 1 FALSE
1 2 TRUE
2 3 FALSE
3 4 TRUE
etc.
``````

Filter the table by Column C, value `TRUE`, and you have what you need on screen.

You can technically extend that method to any `r x c` (row by column) application. My example is a `1 x 2` transformation but you can iterate it as needed.

-