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

[studioblue]

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]

 

[hiker95]

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

joelz,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


You are posting a picture/graphic. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.


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. Are you using a PC or a Mac?
3. a screenshot, NOT a picture/graphic, of the raw data, and, worksheet name
4. a screenshot, NOT a picture/graphic, of the results (manually formatted by you for the results you are looking for)


To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message, with a link to your New Thread, and, I will have a look.

 

[Rick Rothstein]

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;

Put this formula in cell H3 and copy it across to L3, then copy the range H3:L3 down to the bottom of your data...

=IF($B3=LEFT(H$2),$C3,"")



Put this formula in cell N3 and copy it across to R3, then copy the range N3:R3 down to the bottom of your data...

=IF($B3=LEFT(H$2),$D3,"")



Put this formula in cell T3 and copy it across to X3, then copy the range T3:X3 down to the bottom of your data...

=IF($B3=LEFT(N$2),$E3,"")

 

[joelz]

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

Hi Hiker95,

I am using Windows and excel 2007 or 2010. I mean about VBA script. Please following in text data below;

Cheers!

[TABLE="width: 1532"]
<colgroup><col><col span="4"><col span="3"><col span="2"><col><col><col span="12"></colgroup><tbody>[TR]
[TD="colspan: 5"]DATASET[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 17"]RESULT[/TD]
[/TR]
[TR]
[TD]No[/TD]
[TD]ID[/TD]
[TD]height[/TD]
[TD]Width[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD][/TD]
[TD]A_Height[/TD]
[TD]B_Height[/TD]
[TD]C_Height[/TD]
[TD]D_Height[/TD]
[TD]E_Height[/TD]
[TD][/TD]
[TD]A_width[/TD]
[TD]B_width[/TD]
[TD]C_width[/TD]
[TD]D_width[/TD]
[TD]E_width[/TD]
[TD][/TD]
[TD]A_value[/TD]
[TD]B_value[/TD]
[TD]C_value[/TD]
[TD]D_value[/TD]
[TD]E_value[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]79[/TD]
[TD]5.88[/TD]
[TD]6210.51[/TD]
[TD][/TD]
[TD][/TD]
[TD]79[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]5.88[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]6210.51[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]813[/TD]
[TD]5.88[/TD]
[TD]6210.51[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]813[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.88[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]6210.51[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]87[/TD]
[TD]10.56[/TD]
[TD]5591.27[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]87[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10.56[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5591.27[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]281[/TD]
[TD]8.75[/TD]
[TD]5973.33[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]281[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.75[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5973.33[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]B[/TD]
[TD]654[/TD]
[TD]5.97[/TD]
[TD]6186.2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]654[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]5.97[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]6186.2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]D[/TD]
[TD]246[/TD]
[TD] [/TD]
[TD]6186.2[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]246[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6186.2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]A[/TD]
[TD]281[/TD]
[TD]11.1[/TD]
[TD]5542.98[/TD]
[TD][/TD]
[TD][/TD]
[TD]281[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]11.1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]5542.98[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]A[/TD]
[TD]316[/TD]
[TD]6.06[/TD]
[TD]6162.13[/TD]
[TD][/TD]
[TD][/TD]
[TD]316[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]6.06[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]6162.13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]C[/TD]
[TD]584[/TD]
[TD]6.06[/TD]
[TD]6162.13[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]584[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6.06[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6162.13[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]E[/TD]
[TD]252[/TD]
[TD]11.75[/TD]
[TD]5445.87[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]252[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]11.75[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5445.87[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]C[/TD]
[TD]28[/TD]
[TD]6.15[/TD]
[TD]6138.56[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]28[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6.15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6138.56[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]B[/TD]
[TD]822[/TD]
[TD]6.15[/TD]
[TD]6138.56[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]822[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]6.15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD]6138.56[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]D[/TD]
[TD]78[/TD]
[TD]8.27[/TD]
[TD]5877.51[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]78[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]8.27[/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5877.51[/TD]
[TD]






[/TD]
[/TR]
</tbody>[/TABLE]

 

[hiker95]

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

joelz,

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


Your dataset, and, results are completely different from the original thread by studioblue.


And, I can not tell what cells, rows, and, columns your raw data, and, results are in based on your text display.


Please follow my instructions in my reply #11.

Then send me a Private Message, with a link to your New Thread, and, I will have a look.