Macro to copy comma separated values in separate rows?

[Karthik-Excelsior]

Hi Excel gurus,

I've an excel file like this (click on the hyperlink)

I would like to separate the comma separated values in Col. B as separate values in separate rows one after the other. You can look at the 'Desired Output' as shown on the spreadsheet.

LMK if this can be done with the help of a macro.. I've a lot of entries like this, which need to be arranged in separate rows.

Thanks much in advance!

Karthik

 

[patel45]

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
destcol = 5
destrow = 2
For j = 2 To LR
  Cells(destrow, destcol).Value = Cells(j, 1)
  regio = Cells(j, 2)
  arr = Split(regio, ", ")
  For i = 0 To UBound(arr)
    Cells(destrow, destcol + 1).Value = arr(i)
    destrow = destrow + 1
  Next
Next
End Sub

 

[Karthik-Excelsior]

Hi Patel,

Thanks SO MUCH!

That worked like M.A.G.I.C.!

Much appreciated.