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

[kabeldirect]

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!

 

[petrdusek]

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:

A	2	1	10	4	12	3	38	4	40	3	41	3	53	4	57.5	2	69	4	72	2
B	0.5	1	4	3	19	4	21	3	44	4	55	2	72	4	0	0	0	0	0	0

<tbody>
</tbody>

and so on. thank you again for your time.

 

[hiker95]

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.

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?

 

[hiker95]

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

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.

 

[petrdusek]

hiker95,

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

A	2	1	10	4	12	3	38	4	40	3	41	3	53	4	57.5	2	69	4	72	2
B	0.5	1	4	3	19	4	21	3	44	4	55	2	72	4	57.5	2	69	4	72	2
C	2.4	1	20	4	22	3	30	4	2.4	1	20	4	22	3	30	4	2.4	1	20	4
D	0.7	1	47	4	48	3	56	2	80	4	55	22	3	30	4	2.4	1	3	44	4

<tbody>
</tbody>

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.

 

[hiker95]

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.

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

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.

 

[petrdusek]

petrdusek,

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

that worked perfectly, thank you!

 

[hiker95]

petrdusek,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[gopianantakrishna]

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

AB	BC	CD	DE	XYZ	123	576
BA	NF	AD	GD	XYZ	123	576

<tbody>
</tbody>

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

AB	BC	CD	DE	XYZ
AB	BC	CD	DE	123
AB	BC	CD	DE	567
BA	NF	AD	GD	XYZ
BA	NF	AD	GD	123
BA	NF	AD	GD	576

<tbody>
</tbody>

Can you please help me with code

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

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.

 

[hiker95]

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.

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.

 

[hiker95]

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.

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.