Moving multiple columns data to single column

  • Thread starter Thread starter Riaz
  • Start date Start date
R

Riaz

Hi,
Ive been stuck at this thing for a while now. I found ways to do the
vice versa but not this.

I have text in 180 columns (40 rows in each column). I need to move
all the text from B1:B40 C1:C40 ... below the text in Column A (should
start at A41). So everything shows up in one column!

Another scenario is that I have 180 rows of text in Column A. I need
to take each cell and make 39 more copies of it in the same column.

For example:

Column A
123
234
456

Should show up as:

Column A
123
123
123
..
..
..
234
234
234
..
..
..
456
456
456
..
..
..

So basically each text cell is repeated 40 times before the next one
shows up.
Any help in these matters would be greatly appreciated!
Thanks
Riaz
 
For your first scenario - In cell A41:-

=OFFSET($A$1,FLOOR((ROW()-41)/180,1),MOD(ROW()-41,180))

then copy down to A7240
 
And for your second, with your data in A1:A180, in cell B1 put the following:-

=OFFSET($A$1,FLOOR(ROW()/40,1),0)

then copy down to B7200
 
Riaz said:
Hi,
Ive been stuck at this thing for a while now. I found ways to do the
vice versa but not this.

I have text in 180 columns (40 rows in each column). I need to move
all the text from B1:B40 C1:C40 ... below the text in Column A (should
start at A41). So everything shows up in one column!
[snip]
Click to expand...

If the functions in the downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
array enter the following into A41:A7200

=ArrayReshape(b1:fx40,40*179,1,"c")

Alan Beban
 
Back
Top