Quick excel help

[rOAdeh]

Hi guys,

I'm trying to reorganise some data in Excel - I'm sure its possible to do what I want, I just don't know how

Basically I have about 500 rows of data under the header list in the format:

<blank line>
<blank line>
1
2
3

This repeats throughout as seen on the left side of the image - what I want to do is move that data to the format shown on the right hand of the pic - with 3 different columns for each row. How can this be done?

Thanks a lot,

 

[div0]

The best way would be some sort of Macro, especially if you are going to be repeating this procedure a lot with different data.

But if you are only doing this process once want to avoid using a macro, then this might help get you started.

* Select the column of data (NOTE: don't select the entire column in Excel, just select to the end of the data)

* Copy the selection

* Go to another worksheet, select a single cell and then, Edit - Paste Special

* In the Paste Special options tick 'Transpose' and then ok.


Your data is now displayed horizontally instead of vertically. And it shouldn't be too hard to arrange it into the format you want from there.

 

[rOAdeh]

Hi div0,

Thanks for the reply - I've actually managed to do it using

=OFFSET($A$1,(ROW()-1)*5+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

It returns the blank lines as 0 but I can easily cut them out (or do a find/replace).

There's a second bit I now need to do but I'll try and sort that myself before asking here again

Cheers,