Lots of rows and columns into just rows

[Bodster]

I have a sheet of data 3000+ rows with 8 columns in each. I need to adjust
the data into 24000 rows of one column. I want to take the first row of the
old sheet (8 columns) and make the contents the first 8 rows of the new
sheet (1 column), the old sheet second row to be new rows etc...9-16

Something to do with Paste Special but not sure.

Any help please?

Cheers,

Bodster

 

[Ragdyer]

Say data is on Sheet1, from A1 to H3000.

Enter this formula *anywhere* you wish on another sheet, and copy down 3000
rows:

=INDEX(Sheet1!$A$1:$H$3000,ROWS($1:8)/8,MOD(ROWS($1:1)-1,8)+1)

 

[Gord Dibben]

Typo Patrol!

Maybe "copy down 24000 rows"?

Gord

Say data is on Sheet1, from A1 to H3000.

Enter this formula *anywhere* you wish on another sheet, and copy down 3000
rows:

=INDEX(Sheet1!$A$1:$H$3000,ROWS($1:8)/8,MOD(ROWS($1:1)-1,8)+1)

Gord Dibben MS Excel MVP

 

[Bodster]

Thank you so much for that, it'll save loadsa time. I adjusted the formula
as the original sheet contined 11 columns of data.
Can you help with this part now?
The original sheet had dates in Column E (in the form Jan-03). Thay have
come through as 5 digit figures such as 37622. Is there any way of getting
the original form of the date back, affecting just rows 5, 16, 27 etc...of
the new sheet?
A similar problem exists with column I of the original where the £ sign has
been lost. Again is there any way of getting this sign back in the new sheet
just on rows 9, 20, 31 etc..
Cheers,
Bodster

 

[Ragdyer]

OK Gord - I'll bite!

WHY copy down 24,000 rows?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Typo Patrol!

Maybe "copy down 24000 rows"?

Gord

 

[Ragdyer]

Never mind - It just hit me!

You're absolutely right.<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Typo Patrol!

Maybe "copy down 24000 rows"?

Gord

 

[Ragdyer]

The formats will not come across with the formula returns.

What you'll have to do however, is not too difficult.

Since you say you have 11 columns, format the *first set* of 11 rows on your
new sheet exactly as you wish them to display.
Select these 11 rows.
Click on the 'format painter' (yellow paint brush) icon in your tool bar.
Click in the row 12 cell (*not* the fill handle) and drag down as needed.

You'll see that the formats of the original 11 rows will duplicate down the
column in the proper sequence.

 

[Gord Dibben]

Thanks RD

I was going to pull out the TI calculator to test my 24000 number<g>


Gord

 

[Ragdyer]

If we want to really be picky, we should say -
"Copy down 23,999 rows" - right?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Thanks RD

I was going to pull out the TI calculator to test my 24000 number<g>


Gord