Very simple but would save me a hell of a lot of copy and pasting.

Ive got a spreadsheet that has a list of names in each column. i.e. Col A
has Rob in rows 1-4, B has Cesc in 1-4, C has Robin in 1-4, D has Gael in 1-4
etc.

I need to see these in a single column. So, row E will become Rob in E1 -
E4, Cesc in E5-E8, Robin in E9-E12 and Gael in E13 - E16

Many thanks to any genuis that can help me..
Rob
--
Rob Gaffney

One way

In E1:
=OFFSET($A$1,MOD(ROWS($1:1)-1,4),INT((ROWS($1:1)-1)/4))
Copy down to E16
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gaffnr" wrote:
Very simple but would save me a hell of a lot of copy and pasting.

Ive got a spreadsheet that has a list of names in each column. i.e. Col A
has Rob in rows 1-4, B has Cesc in 1-4, C has Robin in 1-4, D has Gael in 1-4
etc.

I need to see these in a single column. So, row E will become Rob in E1 -
E4, Cesc in E5-E8, Robin in E9-E12 and Gael in E13 - E16

Many thanks to any genuis that can help me..
Rob
--
Rob Gaffney

thanks max. can confirm it works for 4 columns. What about if its 32 cols -
what part of the formula do i change? Ive tried the obvious one of 4 but it
didnt work
Really appreciate your help.
--
Rob Gaffney


"Max" wrote:

One way

In E1:
=OFFSET($A$1,MOD(ROWS($1:1)-1,4),INT((ROWS($1:1)-1)/4))
Copy down to E16
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gaffnr" wrote:
Very simple but would save me a hell of a lot of copy and pasting.

Ive got a spreadsheet that has a list of names in each column. i.e. Col A
has Rob in rows 1-4, B has Cesc in 1-4, C has Robin in 1-4, D has Gael in 1-4
etc.

I need to see these in a single column. So, row E will become Rob in E1 -
E4, Cesc in E5-E8, Robin in E9-E12 and Gael in E13 - E16

Many thanks to any genuis that can help me..
Rob
--
Rob Gaffney

The "4" is referring to the number of source rows, not the number of cols.

If you have 32 cols but still 4 rows per col, its essentially the same
formula. But you should strip it down either starting in say, A6 (any row
below row 4). Or neater still, do it in another sheet.

Eg If the source data is in Sheet1,

In Sheet2,
Put in say, B1:
=OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,4),INT((ROWS($1:1)-1)/4))
then copy down until zeros appear signalling exhaustion of source data.

If you start in A6 in the same sheet as the source, just use the earlier
formula, and copy down as far as required.

Adjust the "4" in the formula to suit the number of source rows involved
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gaffnr" wrote:
thanks max. can confirm it works for 4 columns. What about if its 32 cols -
what part of the formula do i change? Ive tried the obvious one of 4 but it
didnt work
Really appreciate your help.
--
Rob Gaffney

Hi Max
im being really thick here but i just dont get it - these are all new
formulas to me.
Perhaps my example of 4 columns by 4 rows was to simple.
The reality is that my current sheet is 32 rows and 35 columns and i want to
see in any column, everything in column a, followed by everything in col b
all the way to col 35.(AI).
I need to repeat this excercise and the next time it may be 10 rows by 40
columns or 15 rows by 5 columns so i need to know which part of the formula
represents number of rows and which represents number of cols so i can adjust
the forumla each time.
Thanks
Rob
--
Rob Gaffney


"Max" wrote:

The "4" is referring to the number of source rows, not the number of cols.

If you have 32 cols but still 4 rows per col, its essentially the same
formula. But you should strip it down either starting in say, A6 (any row
below row 4). Or neater still, do it in another sheet.

Eg If the source data is in Sheet1,

In Sheet2,
Put in say, B1:
=OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,4),INT((ROWS($1:1)-1)/4))
then copy down until zeros appear signalling exhaustion of source data.

If you start in A6 in the same sheet as the source, just use the earlier
formula, and copy down as far as required.

Adjust the "4" in the formula to suit the number of source rows involved
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gaffnr" wrote:
thanks max. can confirm it works for 4 columns. What about if its 32 cols -
what part of the formula do i change? Ive tried the obvious one of 4 but it
didnt work
Really appreciate your help.
--
Rob Gaffney

Change both figs in the INT & MOD parts to reflect the number of source rows.
The number of source cols is not used in the expression, it comes in only in
terms of determining how far that the expression should be copied down (total
formula cells required)

If source data is in Sheet1, starting in A1
and data is 32 Rows x 35 Cols

In Sheet2,
Use in any starting cell, say in B1:
=OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,32),INT((ROWS($1:1)-1)/32))
Copy down by 1120* rows to extract all source data
*32 x 35 = 1120

If source data is
.. 10 rows by 40 columns

Use in any starting cell in Sheet2:
=OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,10),INT((ROWS($1:1)-1)/10))
Copy down by 10 x 40 = 400 rows

If source data is
.. or 15 rows by 5 columns

Use in any starting cell in Sheet2:
=OFFSET(Sheet1!$A$1,MOD(ROWS($1:1)-1,15),INT((ROWS($1:1)-1)/15))
Copy down by 15 x 5 = 75 rows


Hope the above clarifies it better
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gaffnr" wrote:
Hi Max
im being really thick here but i just dont get it - these are all new
formulas to me.
Perhaps my example of 4 columns by 4 rows was to simple.
The reality is that my current sheet is 32 rows and 35 columns and i want to
see in any column, everything in column a, followed by everything in col b
all the way to col 35.(AI).
I need to repeat this excercise and the next time it may be 10 rows by 40
columns or 15 rows by 5 columns so i need to know which part of the formula
represents number of rows and which represents number of cols so i can adjust
the forumla each time.
Thanks
Rob
--
Rob Gaffney