Convert a single row to multiple rows with the same A column value

S

studioblue

New Member
Joined
Nov 9, 2012
Messages
7
Hi,

I am trying to convert many rows like these:

[TABLE="width: 644"]
<tbody>[TR]
[TD="width: 141"]140102800[/TD]
[TD="width: 70, align: right"]28.28[/TD]
[TD="width: 76, align: right"]10.61[/TD]
[TD="width: 72, align: right"]10.61[/TD]
[TD="width: 72, align: right"]14.14[/TD]
[TD="width: 69, align: right"]12.67[/TD]
[TD="width: 72, align: right"]5.89[/TD]
[TD="width: 72, align: right"]8.25[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 644"]
<tbody>[TR]
[TD="width: 141"]140202500[/TD]
[TD="width: 70, align: right"]18.68[/TD]
[TD="width: 76, align: right"]7.01[/TD]
[TD="width: 72, align: right"]7.01[/TD]
[TD="width: 72, align: right"]9.34[/TD]
[TD="width: 69, align: right"]8.37[/TD]
[TD="width: 72, align: right"]3.89[/TD]
[TD="width: 72, align: right"]5.45[/TD]
[/TR]
</tbody>[/TABLE]

into a multiple row like this:


[TABLE="width: 644"]
<tbody>[TR]
[TD="width: 141"][TABLE="width: 500"]
<tbody>[TR]
[TD]140102800[/TD]
[TD]28.28[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]140102800[/TD]
[TD]10.61[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]140102800[/TD]
[TD]10.61[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]140102800[/TD]
[TD]14.14[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]140102800[/TD]
[TD]12.67[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]140102800[/TD]
[TD]5.89[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]140102800[/TD]
[TD]8.25[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="width: 141"]140202500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]18.68[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]140202500[/TD]
[TD]7.01[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 70"][/TD]
[/TR]
</tbody>[/TABLE]
etc.

Thanks so much for your HELP!!
[TABLE="width: 644"]
<tbody>[TR]
[TD="width: 141"][/TD]
[TD="width: 70"][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Re: Convert a single row to multiple rows with the same A column value HELP!!

studioblue,


I assume this is what your raw data looks like (cells, rows, columns):


Excel Workbook
ABCDEFGH
114010280028.2810.6110.6114.1412.675.898.25
214020250018.687.017.019.348.373.895.45
3
4
5
6
7
8
9
10
11
12
13
14
15
Sheet1





After the macro:


Excel Workbook
ABCDEFGH
114010280028.281
214010280010.612
314010280010.613
414010280014.144
514010280012.675
61401028005.896
71401028008.257
814020250018.681
91402025007.012
101402025007.013
111402025009.344
121402025008.375
131402025003.896
141402025005.457
15
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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 12/07/2012
' http://www.mrexcel.com/forum/excel-questions/673505-convert-single-row-multiple-rows-same-column-value-help.html
Dim r As Long, lr As Long, lc As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  lc = Cells(r, Columns.Count).End(xlToLeft).Column
  n = lc - 1
  Rows(r + 1).Resize(n - 1).Insert
  Cells(r + 1, 1).Resize(n - 1).Value = Cells(r, 1).Value
  Cells(r, 2).Resize(n).Value = Application.Transpose(Cells(r, 2).Resize(, n).Value)
  Cells(r, 3).Resize(, n - 1).Clear
  Cells(r, 3) = 1
  With Range(Cells(r + 1, 3), Cells(r + n - 1, 3))
    .FormulaR1C1 = "=R[-1]C+1"
    .Value = .Value
  End With
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
Re: Convert a single row to multiple rows with the same A column value HELP!!

Thanks for helping. I do get a run-time error '1004' though.

It is pointing at the line :

Rows(r + 1).Resize(n - 1).Insert

Not sure why.



hiker95 said:
studioblue,


I assume this is what your raw data looks like (cells, rows, columns):


Sheet1

*ABCDEFGH
********
********
********
********
********
********
********
********
********
********
********
********
********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]28.28[/TD]
[TD="align: right"]10.61[/TD]
[TD="align: right"]10.61[/TD]
[TD="align: right"]14.14[/TD]
[TD="align: right"]12.67[/TD]
[TD="align: right"]5.89[/TD]
[TD="align: right"]8.25[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]18.68[/TD]
[TD="align: right"]7.01[/TD]
[TD="align: right"]7.01[/TD]
[TD="align: right"]9.34[/TD]
[TD="align: right"]8.37[/TD]
[TD="align: right"]3.89[/TD]
[TD="align: right"]5.45[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

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

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

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

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

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

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4




After the macro:


Sheet1

*ABCDEFGH
*****
*****
*****
*****
*****
*****
*****
*****
*****
*****
*****
*****
*****
*****
********

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:91px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:63px;"><col style="width:56px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]28.28[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]10.61[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]10.61[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]14.14[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]12.67[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]5.89[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]140102800[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]18.68[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]7.01[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]7.01[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]9.34[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]8.37[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]3.89[/TD]
[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]140202500[/TD]
[TD="align: right"]5.45[/TD]
[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4




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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ReorgData()
' hiker95, 12/07/2012
' http://www.mrexcel.com/forum/excel-questions/673505-convert-single-row-multiple-rows-same-column-value-help.html
Dim r As Long, lr As Long, lc As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  lc = Cells(r, Columns.Count).End(xlToLeft).Column
  n = lc - 1
  Rows(r + 1).Resize(n - 1).Insert
  Cells(r + 1, 1).Resize(n - 1).Value = Cells(r, 1).Value
  Cells(r, 2).Resize(n).Value = Application.Transpose(Cells(r, 2).Resize(, n).Value)
  Cells(r, 3).Resize(, n - 1).Clear
  Cells(r, 3) = 1
  With Range(Cells(r + 1, 3), Cells(r + n - 1, 3))
    .FormulaR1C1 = "=R[-1]C+1"
    .Value = .Value
  End With
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.
Click to expand...
 
Upvote 0
Re: Convert a single row to multiple rows with the same A column value HELP!!

Does each row of data always end in the same column (like your example shows)?
 
Upvote 0
Re: Convert a single row to multiple rows with the same A column value HELP!!

My bad, the sheet was full :) Thanks so much for your help.
 
Upvote 0
Re: Convert a single row to multiple rows with the same A column value HELP!!

studioblue,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.
 
Upvote 0
Re: Convert a single row to multiple rows with the same A column value HELP!!

Hello,

Thanks for the VBA, unforfunately it's not working for me.
I have something like:

A 126 2 124 8 125 4
B 567 4 836 2 586 1

And I would like it to be:

A 126 2
A 124 8
A 125 4
B 567 4
B 836 2
B 586 1

Can someone please help me how to do this?

Thanks a lot!
 
Upvote 0
Re: Convert a single row to multiple rows with the same A column value HELP!!

kabeldirect,

Welcome to the MrExcel forum.

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

In your New Thread include:
1. the version of Excel you are using
2. a screenshot of the raw data, and, worksheet name
3. a screenshot of the results (manually formatted by you for the results you are looking for), and worksheet name

To post screenshots of your worksheet(s), you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Then send me a Private Message, with a link to your New Thread, and, I will have a look.
 
Last edited:
Upvote 0
Re: Convert a single row to multiple rows with the same A column value HELP!!

Hi,

Please help me and the data scenario as follow;


di-1014134580901.jpg
[/IMG]


Appreciate your help in advance.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Deepakchoudhary
  • Question Question
Database & Bar Chart preparation for comparison of 3 parameters, 5 items on Dynamic selection of OUTLETS
Replies
0
Views
121
Deepakchoudhary
Deepakchoudhary
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
282
Peter_SSs
Peter_SSs
O
  • Solved
I want it all! Using single lookup command to return both multiple Rows and Columns
Replies
4
Views
635
Fluff
Fluff
Allen_Mead
  • Solved
TEXTJOIN 2 columns over multiple rows
Replies
3
Views
537
Fluff
Fluff
A
  • Solved
Count of Values in Single Column
Replies
5
Views
360
Fluff
Fluff

Forum statistics

Threads
1,226,796
Messages
6,193,048
Members
453,772
Latest member
aastupin

Share this page

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