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

embarker

New Member
Joined
Jan 15, 2015
Messages
2
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

ABCDEFGHIJKL

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Record 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]A, A1, A2, A3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]B, B1, B2, B3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]C, C1, C2, C3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]D, D1, D2, D3[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Record 2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]M, M1, M2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]N, N1, N2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]O, O1, O2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]P, P1, P2[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]W, W1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]X, X1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]Z, Z1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

</tbody>


I would like it to transform to be:


ABCDEFGHIJKL

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]Record 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]A[/TD]
[TD="bgcolor: #FFFFFF, align: right"]B[/TD]
[TD="bgcolor: #FFFFFF, align: right"]C[/TD]
[TD="bgcolor: #FFFFFF, align: right"]D[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Record 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]A1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]B1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]C1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]D1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]A2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]B2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]C2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]D2[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]A3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]B3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]C3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]D3[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]M[/TD]
[TD="bgcolor: #FFFFFF, align: right"]N[/TD]
[TD="bgcolor: #FFFFFF, align: right"]O[/TD]
[TD="bgcolor: #FFFFFF, align: right"]P[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]M1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]N1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]O1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]P1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]M2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]N2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]O2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]P2[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]W[/TD]
[TD="bgcolor: #FFFFFF, align: right"]X[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]Z[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]Record 3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]W1[/TD]
[TD="bgcolor: #FFFFFF, align: right"]X1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"]Z1[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

</tbody>


Thanks for any assistance that may be possible.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
ABCDE
1Record 1A, A1, A2, A3B, B1, B2, B3C, C1, C2, C3D, D1, D2, D3
2Record 2M, M1, M2N, N1, N2O, O1, O2P, P1, P2
3Record 3W, W1X, X1Z, Z1
4
5
6
7
8
9
10
Sheet1


After the macro:


Excel 2007
ABCDE
1Record 1ABCD
2Record 1A1B1C1D1
3Record 1A2B2C2D2
4Record 1A3B3C3D3
5Record 2MNOP
6Record 2M1N1O1P1
7Record 2M2N2O2P2
8Record 3WXZ
9Record 3W1X1Z1
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.

Code:
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.
 
Upvote 0
Thank you so much, hiker95. I was able to run this on Windows 8.1 with Excel 2013 and it worked perfectly! Much appreciated.
 
Upvote 0
embarker,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top