I need to take one column as Key and other as values, and get resulting sheet with one key one value.

[anujpatel0718]

Question,
Column A has key values. In column B, few rows just have one values and few have multiple values. I.e.
A B
1 x
2 x,y,z
3 y,z,e
4 n,m
I need the resulting sheet as follows,
A B
1 x
2 x
2 y
2 z
3 y
3 z
3 e
4 n
4 m


I have a huge list for this,
If anyone can help.

 

[hiker95]

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
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
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.

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.

 

[anujpatel0718]

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
x
x,y,z
y,z,e
n,m

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

After the macro:

Excel 2007

	A	B
x
x
y
z
y
z
e
n
m

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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.

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.

Version is 2010 and windows 7.
Let me try and I will get back to you.

 

[hiker95]

anujpatel0718,

Version is 2010 and windows 7.
Let me try and I will get back to you.

Will be looking for your reply.


1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.