Separate numbers into separate cells on another sheet

[bdavis99]

Hi, first time poster here...go easy! LOL
I have a spreadsheet and in the asset column i have a list of numbers related to that one line of information. All separated by a comma

Number	Title	Rev	Asset	total amount of assets
UC02-WPD-UT-REP-N001-000012	Network Design - N1	P01	WMI-4A-0056, WMI-4A-0063, WMI-4A-0063B, WMI-4A-0063A, WMI-4A-0062	5

<tbody>
</tbody>

My boss has now asked me to list out each Asset number separately, so i need 5 lines, but still with number, title, and rev info showing in each line. I have over 1000 separate assets, to do it manually would take ages. Is there a formula i can use.
Any help would be very much appreciated. SOrry if it is not explained very well

 

[mumps]

Try this macro. Change the sheet names (in red) to suit your needs.

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, asset As Range, splitAssest As Variant, i As Long
    Dim bottomD As Long, bottomA As Long
    Set srcWS = Sheets("[COLOR="#FF0000"]Sheet1[/COLOR]")
    Set desWS = Sheets("[COLOR="#FF0000"]Sheet2[/COLOR]")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Range("A1:D1") = Array("Number", "Title", "Rev", "Asset")
    For Each asset In srcWS.Range("D2:D" & LastRow)
        splitAssest = Split(asset, ", ")
        For i = LBound(splitAssest) To UBound(splitAssest)
            desWS.Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = splitAssest(i)
        Next i
        With desWS
            bottomA = .Range("A" & .Rows.Count).End(xlUp).Row
            bottomD = .Range("D" & .Rows.Count).End(xlUp).Row
            .Range("A" & bottomA + 1 & ":A" & bottomD) = asset.Offset(0, -3)
            .Range("B" & bottomA + 1 & ":B" & bottomD) = asset.Offset(0, -2)
            .Range("C" & bottomA + 1 & ":C" & bottomD) = asset.Offset(0, -1)
        End With
    Next asset
    Application.ScreenUpdating = True
End Sub