moving the contents of a cell
 

I am trying to figure out how to take the contents of cells which are
across a few columns and move them all into one long column.

Example:

|column1| |column2| |column3|
|dog.......| |bird.......| |cat.........|
|car........| |truck.....| |bus........|

Into

|column4|
dog
bird
cat
car
truck
bus

It seems like it shouldn't be too hard, but I am pretty new to excel so
any help would be greatly appreciated.

Thanks,
Jen


--
JenBasch
------------------------------------------------------------------------
JenBasch's Profile: http://www.excelforum.com/member.php...o&userid=27369
View this thread: http://www.excelforum.com/showthread...hreadid=469033

Hi!

Try this:

=OFFSET($A$1,INT((ROWS($A$1:A1)-1)/3),MOD(ROWS($A$1:A1)-1,3))

Copy down until you get zeros.

Then do a copy/paste special/values to get rid of the formulas.

Biff

"JenBasch" wrote in
message ...

I am trying to figure out how to take the contents of cells which are
across a few columns and move them all into one long column.

Example:

|column1| |column2| |column3|
|dog.......| |bird.......| |cat.........|
|car........| |truck.....| |bus........|

Into

|column4|
dog
bird
cat
car
truck
bus

It seems like it shouldn't be too hard, but I am pretty new to excel so
any help would be greatly appreciated.

Thanks,
Jen


--
JenBasch
------------------------------------------------------------------------
JenBasch's Profile:
http://www.excelforum.com/member.php...o&userid=27369
View this thread: http://www.excelforum.com/showthread...hreadid=469033

Assume source data is in Sheet1, cols A to C, from row1 down

In Sheet2
-------
Put in any starting cell, say B2:

=OFFSET(Sheet1!$A$1,INT((ROWS($A$1:A1)-1)/3),
MOD(ROWS($A$1:A1)-1,3))

Copy down until zeros appear,
signalling exhaustion of data extracted from Sheet1

Adapt to suit, e.g.: change the number "3" in both the INT(..) and MOD(..)
parts in the formula to say, "5", if you have 5 columns instead
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JenBasch" wrote in
message ...

I am trying to figure out how to take the contents of cells which are
across a few columns and move them all into one long column.

Example:

|column1| |column2| |column3|
|dog.......| |bird.......| |cat.........|
|car........| |truck.....| |bus........|

Into

|column4|
dog
bird
cat
car
truck
bus

It seems like it shouldn't be too hard, but I am pretty new to excel so
any help would be greatly appreciated.

Thanks,
Jen


--
JenBasch
------------------------------------------------------------------------
JenBasch's Profile:
http://www.excelforum.com/member.php...o&userid=27369
View this thread: http://www.excelforum.com/showthread...hreadid=469033