Help with Column to Row formatting

[MadHookUp]

I know very little about excel and stumbled across this forum whil
trying to Google for an answer to my problem. Was hoping you all migh
be able to help.

I have an excel file like this....
A1---------------B2
Bob Jones-----0;234
Judy Smith----12;14

And I would like to convert that to....
A1--------------B2
Bob Jones----0
Bob Jones----234
Judy Smith---12
Judy Smith---14

Is this possible? The B2 column would have far more numbers an
semicolons than the example. I found out ways to separate the sem
colons into columns. And the paste special feature with Tranpose. Bu
that puts all the names into Columns, rather than rows. Any help woul
be much appreciated

 

[Claus Busch]

Hi,

Am Thu, 20 Jun 2013 16:25:16 +0100 schrieb MadHookUp:

A1---------------B2
Bob Jones-----0;234
Judy Smith----12;14

And I would like to convert that to....
A1--------------B2
Bob Jones----0
Bob Jones----234
Judy Smith---12
Judy Smith---14

do it with a macro:

Sub Transpose()
Dim LRow As Long
Dim i As Long
Dim myArr As Variant
Dim rngC As Range

i = 1
With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
For Each rngC In .Range("B1:B" & LRow)
myArr = Split(rngC, ";")
Sheets("Sheet2").Cells(i, 1).Resize(UBound(myArr) + 1, 1) _
= rngC.Offset(0, -1)
Sheets("Sheet2").Cells(i, 2).Resize(UBound(myArr) + 1, 1) _
= WorksheetFunction.Transpose(myArr)
i = i + UBound(myArr) + 1
Next
End With
End Sub


Regards
Claus Busch

 

[MadHookUp]

Claus.... you are my hero