Reputation: 21

Excel - From a matrix to a list

I want to transform a matrix into a single column list.

Lets say I have this matrix:

qq|w|x|y|z
a |1| |2|
b | | |2|3
c |1|3|2|

and I want to make a list from it, like this:

a,1,2
b,2,3
c,1,3,2

edit: I'm using Excel 15.19 (for mac)

Upvotes: 2

Answers (1)

Reputation: 96791

With data in A1 through E4, in F2 enter:

=A2 & "," & B2 & "," & C2 & "," & D2 & "," & E2

and copy down and in G2 enter:

=SUBSTITUTE(SUBSTITUTE(IF(RIGHT(F2,1)=",",MID(F2,1,LEN(F2)-1),F2),",,",","),",,",",")

and copy down:

Upvotes: 1