How to create a list out of wide table

[shir0206]

Hello,

Please advise –I receive the following input (column “INPUT (#1)” in the print screen), and I would like to create a VBA code that creates a list out of it, as described in the output (column “OUTPUT” in the print screen).

So far I I wrote a code that uses “text to columns” and changes “INPUT (#1)” into “INPUT (#2)”.
However, I have no idea how to get to the required “OUTPUT”.

23996

Could anyone help me please?

Thank you very much!

[大灰狼1976]

Hi shir!
No tested because no EXCEL at home.

sub Test()
dim arr, arrTmp, arrRst(1 to 10000, 1 to 2), i&, j&, r&, s$
arr=range("a4:a" & cells(rows.count, 1).end(3).row)
for i=1 to ubound(arr)
s =replace(arr(i,1),"-",",")
arrTmp= split(s,",")
for j=1 to ubound(arrTmp)
r=r+1
arrRst(r,1)=trim(arrTmp(0))
arrRst(r,2)=trim(arrTmp(j))
next j
next i
[j4].resize(r,2)=arrRst
end sub

[shir0206]

Hi shir!
No tested because no EXCEL at home.

sub Test()
dim arr, arrTmp, arrRst(1 to 10000, 1 to 2), i&, j&, r&, s$
arr=range("a4:a" & cells(rows.count, 1).end(3).row)
for i=1 to ubound(arr)
s =replace(arr(i,1),"-",",")
arrTmp= split(s,",")
for j=1 to ubound(arrTmp)
r=r+1
arrRst(r,1)=trim(arrTmp(0))
arrRst(r,2)=trim(arrTmp(j))
next j
next i
[j4].resize(r,2)=arrRst
end sub


Hey,
Unfortunately it doesn't work - I receive compile error that says "Can't find project or library" on "Trim" line

[大灰狼1976]

Hi shir!
Please refer to the attachment.
It works well here.

[shir0206]

Hi shir!
Please refer to the attachment.
It works well here.

YAY! It's perfect! Thank you very much! :)

[大灰狼1976]

It's OK!:)