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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2