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

K

kabeldirect

New Member
Joined
Jun 12, 2013
Messages
3
Hello,

I have seen a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA at http://www.mrexcel.com/forum/excel-...row-multiple-rows-same-column-value-help.html
</acronym>
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!
 
Sort by date Sort by votes
Thank you hiker95 for the quick reply,

would it be possible to use the macro (or modified one) with more data if the row "length" was 21 cells constant?
because the data i need to process is quite complex (more than 100 rows actually), i could add zeroes to the empty cells or
remove the redundant cells after the macro is applied. for example:

[TABLE="width: 901"]
<tbody>[TR]
[TD="align: right"]A[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]57.5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]B[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

and so on. thank you again for your time.
 
Upvote 0
would it be possible to use the macro (or modified one) with more data if the row "length" was 21 cells constant?
because the data i need to process is quite complex (more than 100 rows actually), i could add zeroes to the empty cells or
remove the redundant cells after the macro is applied.
Click to expand...

petrdusek,

1. Would the blank empty pairs of cells, always be blank?

2. Would a pair of cells, say B, and, C, both be blank?

3. Would a pair of cells, say P, and, Q, both be blank?

4. If the pair of cells are blank, the macro can be modified to not process those blank pairs?
 
Upvote 0
Herrybraun said:
I am looking for same plugin or add on for excel. I am using MS 2007 and need a good excel professional assistance.

Herry
Botox Leeds
Click to expand...

Herrybraun,

As soon as I am finished with petrdusek's request, I will return to your request.

This way it will be easier for me to keep track of what is actually being done in this thread.


If that is not acceptable, then:


Please 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. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com


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

lets just assume that the number of cells is always 21, e.g.

[TABLE="width: 1344"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]10[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]12[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]38[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]40[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]41[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]53[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]57.5[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]69[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]72[/TD]
[TD="width: 64, align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]57.5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

and there are more rows after the D row. it would be sufficient for me to process X number of rows with data in this format. thank you.
 
Upvote 0
hiker95,

lets just assume that the number of cells is always 21, e.g.

and there are more rows after the D row. it would be sufficient for me to process X number of rows with data in this format. thank you.
Click to expand...

petrdusek,

Based on the new raw data, and, the above quote, the ReorgData_V2 macro should work correctly.


But, here is a new macro solution for you to consider.

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

Code: 
Sub ReorgData_V3()
' hiker95, 02/01/2016, ME707997
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long, c As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = 21
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (UBound(a, 1) * ((lc - 1) / 2)), 1 To 3)
  For i = 1 To UBound(a, 1)
    For c = 2 To lc Step 2
      j = j + 1
      o(j, 1) = a(i, 1)
      o(j, 2) = a(i, c)
      o(j, 3) = a(i, c + 1)
    Next c
  Next i
  .Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
  .UsedRange.Columns.AutoFit
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData_V3 macro.
 
Last edited:
Upvote 0
petrdusek,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
Hi Hiker95,

I have similar requirement can you please help me with the code.


I have requirement to convert row into multiple rows.
Eg : A B C D E F G
[TABLE="width: 448"]
<tbody>[TR]
[TD]AB[/TD]
[TD="width: 64"]BC[/TD]
[TD="width: 64"]CD[/TD]
[TD="width: 64"]DE[/TD]
[TD="width: 64"]XYZ[/TD]
[TD="width: 64, align: right"]123[/TD]
[TD="width: 64, align: right"]576[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]NF[/TD]
[TD]AD[/TD]
[TD]GD[/TD]
[TD]XYZ[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]576[/TD]
[/TR]
</tbody>[/TABLE]

Output should be like below. First 4 columns are fixed and rows to be created based on remaining columns( E to G , they can be upto N number ).

A B C D E
[TABLE="width: 320"]
<tbody>[TR]
[TD]AB[/TD]
[TD="width: 64"]BC[/TD]
[TD="width: 64"]CD[/TD]
[TD="width: 64"]DE[/TD]
[TD="class: xl65, width: 64"]XYZ[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]BC[/TD]
[TD]CD[/TD]
[TD]DE[/TD]
[TD="class: xl65"]123[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]BC[/TD]
[TD]CD[/TD]
[TD]DE[/TD]
[TD="class: xl65"]567[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]NF[/TD]
[TD]AD[/TD]
[TD]GD[/TD]
[TD="class: xl65"]XYZ[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]NF[/TD]
[TD]AD[/TD]
[TD]GD[/TD]
[TD="class: xl65"]123[/TD]
[/TR]
[TR]
[TD]BA[/TD]
[TD]NF[/TD]
[TD]AD[/TD]
[TD]GD[/TD]
[TD="class: xl65"]576[/TD]
[/TR]
</tbody>[/TABLE]


Can you please help me with code




hiker95 said:
petrdusek,

Based on the new raw data, and, the above quote, the ReorgData_V2 macro should work correctly.


But, here is a new macro solution for you to consider.

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

Code: 
Sub ReorgData_V3()
' hiker95, 02/01/2016, ME707997
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long, c As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = 21
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (UBound(a, 1) * ((lc - 1) / 2)), 1 To 3)
  For i = 1 To UBound(a, 1)
    For c = 2 To lc Step 2
      j = j + 1
      o(j, 1) = a(i, 1)
      o(j, 2) = a(i, c)
      o(j, 3) = a(i, c + 1)
    Next c
  Next i
  .Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
  .UsedRange.Columns.AutoFit
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData_V3 macro.
Click to expand...
 
Upvote 0
gopianantakrishna,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider that is based on your two flat text displays, and, instructions, that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns.

I assume that both worksheets already exist.

You can change the worksheet names in the macro.

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_V4()
' hiker95, 10/12/2017, ME707997
Dim wi As Worksheet, wo As Worksheet
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long, c As Long
Application.ScreenUpdating = False
Set wi = Sheets("Input")    '<-- you can change the sheet name here
Set wo = Sheets("Output")   '<-- you can change the sheet name here
With wi
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (UBound(a, 1) * ((lc - 4))), 1 To 5)
  For i = 1 To UBound(a, 1)
    For c = 5 To lc Step 1
      j = j + 1
      o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(i, 3): o(j, 4) = a(i, 4)
      o(j, 5) = a(i, c)
    Next c
  Next i
End With
With wo
  .UsedRange.Clear
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
  .Activate
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData_V4 macro.
 
Upvote 0
gopianantakrishna,

I may have not understand your requirements.

Here is another macro will that will run in the active worksheet, that uses two arrays in memory, and, will adjust to the number of raw data rows, and, columns.

The results will overwrite the raw data.

With the same instructions as my last reply.

Code: 
Sub ReorgData_V5()
' hiker95, 10/12/2017, ME707997
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Dim lr As Long, lc As Long, c As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  ReDim o(1 To (UBound(a, 1) * ((lc - 4))), 1 To 5)
  For i = 1 To UBound(a, 1)
    For c = 5 To lc Step 1
      j = j + 1
      o(j, 1) = a(i, 1): o(j, 2) = a(i, 2): o(j, 3) = a(i, 3): o(j, 4) = a(i, 4)
      o(j, 5) = a(i, c)
    Next c
  Next i
  .Range(.Cells(1, 1), .Cells(lr, lc)).ClearContents
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).AutoFit
End With
Application.ScreenUpdating = True
End Sub

With the same instructions as my last reply.

Then run the ReorgData_V5 macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Separating multiple text values with a single cell
Replies
8
Views
222
Joe4
Joe4
S
  • Solved
Require the heading date in which column found the first data/value in a specific row.
Replies
4
Views
93
Peter_SSs
Peter_SSs
B
  • Question Question
Even distribution of hours/value across multiple rows
Replies
7
Views
348
BGwayne
B
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
282
Peter_SSs
Peter_SSs
T
  • Solved
Excel formula: how to sum multiple columns on a single criteria
Replies
3
Views
402
TT123
T

Forum statistics

Threads
1,226,834
Messages
6,193,216
Members
453,780
Latest member
enghoss77

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