Converting 3 columns to 1

  • Thread starter Thread starter Gargoyl
  • Start date Start date
G

Gargoyl

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
 
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
 
Thanks!

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

/Nikla
 
Back
Top