anujpatel0718,
Welcome to the MrExcel forum.
1. What version of Excel and Windows are you using?
2. Are you using a PC or a Mac?
Sample raw data in the active worksheet:
Excel 2007
| A | B |
---|
1 | 1 | x |
2 | 2 | x,y,z |
3 | 3 | y,z,e |
4 | 4 | n,m |
5 | | |
6 | | |
7 | | |
8 | | |
9 | | |
10 | | |
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
After the macro:
Excel 2007
| A | B |
---|
1 | 1 | x |
2 | 2 | x |
3 | 2 | y |
4 | 2 | z |
5 | 3 | y |
6 | 3 | z |
7 | 3 | e |
8 | 4 | n |
9 | 4 | m |
10 | | |
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
1. Copy the below code
2. Open your NEW workbook
3. Press the keys
ALT +
F11 to open the Visual Basic Editor
4. Press the keys
ALT +
I to activate the Insert menu
5. Press
M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys
ALT +
Q to exit the Editor, and return to Excel
8. To run the macro from Excel press
ALT +
F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Code:
Sub ReorgData()
' hiker95, 11/12/2014, ME817835
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
With ActiveSheet
lr = .Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
If InStr(.Cells(r, 2), ",") Then
s = Split(Trim(.Cells(r, 2)), ",")
Rows(r + 1).Resize(UBound(s)).Insert
.Cells(r, 2).Resize(UBound(s) + 1) = Application.Transpose(s)
.Cells(r + 1, 1).Resize(UBound(s)) = .Cells(r, 1).Value
End If
Next r
End With
Application.ScreenUpdating = True
End Sub
Before you use the macro with
Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension
.xlsm
Then run the
ReorgData macro.