Convert a single row with multiple columns (each column has multiple data elements) to multiple rows with the same A column value

[embarker]

Building on the discussion here - http://www.mrexcel.com/forum/excel-...ngle-row-multiple-rows-same-column-value.html

I have a data set with a unique key in Column A and then repeating values in the subsequent columns. I would like to transform the repeating values into multiple rows and maintain the unique ID from Column A for each. The number of repeating values may vary from record to record and sometimes a column may be empty.

Sample Data

	A	B	C	D	E	F	G	H	I	J	K	L
1	Record 1	A, A1, A2, A3	B, B1, B2, B3	C, C1, C2, C3	D, D1, D2, D3
2	Record 2	M, M1, M2	N, N1, N2	O, O1, O2	P, P1, P2
3	Record 3	W, W1	X, X1		Z, Z1
4
5
6
7
8
9
10

<tbody>
</tbody>

I would like it to transform to be:

	A	B	C	D	E	F	G	H	I	J	K	L
1	Record 1	A	B	C	D
2	Record 1	A1	B1	C1	D1
3	Record 1	A2	B2	C2	D2
4	Record 1	A3	B3	C3	D3
5	Record 2	M	N	O	P
6	Record 2	M1	N1	O1	P1
7	Record 2	M2	N2	O2	P2
8	Record 3	W	X		Z
9	Record 3	W1	X1		Z1
10

<tbody>
</tbody>

Thanks for any assistance that may be possible.

 

[hiker95]

embarker,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


The following macro is based on the structure of your posted raw data, where each cell in a row in columns B thru E contain the same number of elements.

Sample raw data:

Excel 2007
	A	B	C	D	E
1	Record 1	A, A1, A2, A3	B, B1, B2, B3	C, C1, C2, C3	D, D1, D2, D3
2	Record 2	M, M1, M2	N, N1, N2	O, O1, O2	P, P1, P2
3	Record 3	W, W1	X, X1		Z, Z1
4
5
6
7
8
9
10
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E
1	Record 1	A	B	C	D
2	Record 1	A1	B1	C1	D1
3	Record 1	A2	B2	C2	D2
4	Record 1	A3	B3	C3	D3
5	Record 2	M	N	O	P
6	Record 2	M1	N1	O1	P1
7	Record 2	M2	N2	O2	P2
8	Record 3	W	X		Z
9	Record 3	W1	X1		Z1
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, 01/15/2015, ME829357
Dim r As Long, lr As Long, c As Long, lc As Long
Dim s, i As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For r = lr To 1 Step -1
  For c = 2 To lc Step 1
    If Cells(r, c) <> "" Then
      If InStr(Cells(r, c), ", ") Then
        s = Split(Trim(Cells(r, c)), ", ")
        If c = 2 Then
          Rows(r + 1).Resize(UBound(s)).Insert
          n = UBound(s) + 1
        End If
        Cells(r, c).Resize(n).Value = Application.Transpose(s)
      End If
    End If
  Next c
  Cells(r, 1).Resize(n).Value = Cells(r, 1).Value
Next r
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.

 

[embarker]

Thank you so much, hiker95. I was able to run this on Windows 8.1 with Excel 2013 and it worked perfectly! Much appreciated.

 

[hiker95]

embarker,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.