Sorting Data in Excel

[NNNNN]

A B

1001 ABC, DEF,GHI,JKL
1002 MNO
1003 PQR
1004 STU,VWX
1005 YZ

Is there a way to get the above to
look like the below?


A B

1001 ABC
1001 DEF
1001 GHI
1001 JKL
1002 MNO
1003 PQR
1004 STU
1004 VWX
1005 YZ


Thanks

 

[Loomah]

Hi
To answer your question - Yes, there is a way!
and one possible way is this

Sub SplitAndSortData()
Dim c As Range
Dim strOrig As String
Dim strNew As String

' organise data
For Each c In Worksheets("sheet1").UsedRange.Columns(2).Cells
    strOrig = Trim(c.Text)
    Do While InStr(1, strOrig, &quot;,&quot;) <> 0
        strNew = Right(strOrig, Len(strOrig) - InStrRev(strOrig, &quot;,&quot;))
        strOrig = Left(strOrig, InStrRev(strOrig, &quot;,&quot;) - 1)
        c = Trim(strOrig)
        c.Offset(1).EntireRow.Insert
        c.Offset(1, 0) = Trim(strNew)
        c.Offset(1, -1) = c.Offset(0, -1)
    Loop
Next
    ' sort data
    Worksheets(&quot;sheet1&quot;).UsedRange.Sort Key1:=Range(&quot;A1&quot;), Order1:=xlAscending, Key2:=Range(&quot;B1&quot;) _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?