moving the contents of a cell

[JenBasch]

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

 

[Biff]

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

 

[Max]

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