Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to combine several columns into a single column | Excel Discussion (Misc queries) | |||
Need help with Converting Rows of data into 2 columns | Excel Worksheet Functions | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Converting Columns to Rows | Excel Worksheet Functions | |||
Counting the Contents of Two Columns | Excel Discussion (Misc queries) |