Hi,

Does anyone know how to change a large matrix into one column?

Like this:

abc
abc
abc

into

a
a
a
b
b
b
c
c
c

I am not being to able to solve this puzzle

Jeroen


--
jeroen2
------------------------------------------------------------------------
jeroen2's Profile: http://www.excelforum.com/member.php...o&userid=30863
View this thread: http://www.excelforum.com/showthread...hreadid=505353

Try:

Option Explicit

Sub Matrix()
Dim wss As Worksheet
Dim wsd As Worksheet
Dim rs As Long ' row src
Dim cs As Long ' col src
Dim rd As Long ' row dest

Set wss = ActiveSheet
Set wsd = ActiveWorkbook.Worksheets.Add ' make new sheet
rs = 1
cs = 1
rd = 1

'assume starts in A1
While wss.Cells(rs, 1) < ""
While wss.Cells(rs, cs) < ""
wsd.Cells(rd, 1) = wss.Cells(rs, cs)
rd = rd + 1
cs = cs + 1
Wend
rs = rs + 1
cs = 1
Wend
Set wss = Nothing
Set wsd = Nothing
End Sub

--
HTHs Martin


"jeroen2" wrote:


Hi,

Does anyone know how to change a large matrix into one column?

Like this:

abc
abc
abc

into

a
a
a
b
b
b
c
c
c

I am not being to able to solve this puzzle

Jeroen


--
jeroen2
------------------------------------------------------------------------
jeroen2's Profile: http://www.excelforum.com/member.php...o&userid=30863
View this thread: http://www.excelforum.com/showthread...hreadid=505353

Dear Martin,

Thanks very much for helping.

But I am afraid my limited programming knowledge is not enough to
understand your solution.

So I simply copied it in a macro and runed the macro but it did not
work.

Could you tell me how I should use it?

Jeroen


--
jeroen2
------------------------------------------------------------------------
jeroen2's Profile: http://www.excelforum.com/member.php...o&userid=30863
View this thread: http://www.excelforum.com/showthread...hreadid=505353

Yes, it does work.

The mistake was in my datasheet. I have "" in my datasheet and the
macro uses "" to detect the border of the matrix.

A big thanks

You solved a problem which kept me stuck. Now I can analyze my data,

Jeroen


--
jeroen2
------------------------------------------------------------------------
jeroen2's Profile: http://www.excelforum.com/member.php...o&userid=30863
View this thread: http://www.excelforum.com/showthread...hreadid=505353