Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

on a larger scale

share|improve this question
up vote 2 down vote accepted

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.

enter image description here

share|improve this answer
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:

0 1
1 2
2 3
3 4
5 6

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:

1 2 TRUE
3 4 TRUE

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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .