Converting 3 columns to 1
 

Hi,

Let's suppose the data start at Row 2 (i.e., A2, B2, C2).

In row 2 of another column, say D2, enter the following formula:

=OFFSET($A$2,QUOTIENT(ROW()-2,3),MOD(ROW()-2,3))

and drag the formula down the column to thrice as many rows as in the matrix.

NOTE:
If the data start at some other row number, say 'n' (e.g., A1, B1, and C1,
or A3, B3, and C3), you have to modify the formula accordingly.

=OFFSET($A$n,QUOTIENT(ROW()-n,3),MOD(ROW()-n,3))

Also note that the new column is still linked to the original matrix. To
make it independent, select column C -- "Edit" -- "Copy" -- "Edit" --
"Paste Special" -- check "Values" under "Paste" -- "OK"

Regards,
B. R. Ramachandran



"Gargoyl" wrote:


I have a list of data in a matrix with three columns, like so

John 3 blue
Mary 7 red
Mark 4 white
... and so on

I want to convert this matrix into a list with just one column

John
3
blue
Mary
7
red
Mark
4
white
... and so on

Does anyone know of a easy way of doing this?

Very thankful for any help!

/Niklas


--
Gargoyl
------------------------------------------------------------------------
Gargoyl's Profile: http://www.excelforum.com/member.php...o&userid=29280
View this thread: http://www.excelforum.com/showthread...hreadid=490014

Converting 3 columns to 1
 

Thanks!

I have a swedish version of Excel and the QUOTIENT function didn't work
so I had to use ordinary division. But it all worked out fine in the
end, thank you very much for the help!

/Niklas


--
Gargoyl
------------------------------------------------------------------------
Gargoyl's Profile: http://www.excelforum.com/member.php...o&userid=29280
View this thread: http://www.excelforum.com/showthread...hreadid=490014