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

[studioblue]

Hi,

I am trying to convert many rows like these:

140102800

28.28

10.61

10.61

14.14

12.67

5.89

8.25

<tbody>
</tbody>

140202500

18.68

7.01

7.01

9.34

8.37

3.89

5.45

<tbody>
</tbody>

into a multiple row like this:

140102800 28.28 1 140102800 10.61 2 140102800 10.61 3 140102800 14.14 4 140102800 12.67 5 140102800 5.89 6 140102800 8.25 7 140202500 <tbody> </tbody> 18.68 1 140202500 7.01 2 <tbody> </tbody>

<tbody>
</tbody>

etc.

Thanks so much for your HELP!!

<tbody>
</tbody>

 

[hiker95]

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
	A	B	C	D	E	F	G	H
1	140102800	28.28	10.61	10.61	14.14	12.67	5.89	8.25
2	140202500	18.68	7.01	7.01	9.34	8.37	3.89	5.45
3
4
5
6
7
8
9
10
11
12
13
14
15
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1	140102800	28.28	1
2	140102800	10.61	2
3	140102800	10.61	3
4	140102800	14.14	4
5	140102800	12.67	5
6	140102800	5.89	6
7	140102800	8.25	7
8	140202500	18.68	1
9	140202500	7.01	2
10	140202500	7.01	3
11	140202500	9.34	4
12	140202500	8.37	5
13	140202500	3.89	6
14	140202500	5.45	7
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.

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.

 

[studioblue]

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.

studioblue,


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


Sheet1

*	A	B	C	D	E	F	G	H
1	140102800	28.28	10.61	10.61	14.14	12.67	5.89	8.25
2	140202500	18.68	7.01	7.01	9.34	8.37	3.89	5.45
3	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*

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

Excel tables to the web >> Excel Jeanie HTML 4




After the macro:


Sheet1

*	A	B	C	D	E	F	G	H
1	140102800	28.28	1	*	*	*	*	*
2	140102800	10.61	2	*	*	*	*	*
3	140102800	10.61	3	*	*	*	*	*
4	140102800	14.14	4	*	*	*	*	*
5	140102800	12.67	5	*	*	*	*	*
6	140102800	5.89	6	*	*	*	*	*
7	140102800	8.25	7	*	*	*	*	*
8	140202500	18.68	1	*	*	*	*	*
9	140202500	7.01	2	*	*	*	*	*
10	140202500	7.01	3	*	*	*	*	*
11	140202500	9.34	4	*	*	*	*	*
12	140202500	8.37	5	*	*	*	*	*
13	140202500	3.89	6	*	*	*	*	*
14	140202500	5.45	7	*	*	*	*	*
15	*	*	*	*	*	*	*	*

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

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.

 

[Rick Rothstein]

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)?

 

[studioblue]

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.

 

[studioblue]

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

Yes sir.

 

[hiker95]

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.

 

[kabeldirect]

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!

 

[hiker95]

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.

 

[joelz]

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;

[/IMG]


Appreciate your help in advance.

Cheers!