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
Another sample:

[TABLE="width: 487"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1,35[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0,99[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0,72[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9,07[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]8,23[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]7,48[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]1,35[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0,99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0,72[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9,07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]8,23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]7,48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
kabeldirect,

Sample raw data (with nothing else in the worksheet in the columns to the right):


Excel 2007
ABCDEFGHIJKL
1A401,35800,991500,72
2B99,07188,23407,48
3
4
5
6
7
8
9
10
Sheet1


After the macro:


Excel 2007
ABCDEFGHIJKL
1A401,35800,991500,72A401,35
2B99,07188,23407,48A800,99
3A1500,72
4B99,07
5B188,23
6B407,48
7
8
9
10
Sheet1


If you add more raw data in columns A thru G:


Excel 2007
ABCDEFGHIJKL
1A401,35800,991500,72A401,35
2B99,07188,23407,48A800,99
3C1500,72401,35188,23A1500,72
4B99,07
5B188,23
6B407,48
7
8
9
10
Sheet1


And, run the macro again:


Excel 2007
ABCDEFGHIJKL
1A401,35800,991500,72A401,35
2B99,07188,23407,48A800,99
3C1500,72401,35188,23A1500,72
4B99,07
5B188,23
6B407,48
7C1500,72
8C401,35
9C188,23
10
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, 06/12/2013
' http://www.mrexcel.com/forum/excel-questions/707997-convert-single-row-multiple-rows-same-column-value.html
Dim a As Variant, b As Variant
Dim lr As Long, lc As Long, luc As Long
Dim i As Long, ii As Long, c As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, 1).End(xlToRight).Column
luc = Cells(1, Columns.Count).End(xlToLeft).Column
If luc > lc Then
  Range(Cells(1, lc + 3), Cells(1, luc)).Columns.ClearContents
End If
a = Range(Cells(1, 1), Cells(lr, lc))
ReDim b(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
    ii = ii + 1
    b(ii, 1) = a(i, 1)
    b(ii, 2) = a(i, c)
    b(ii, 3) = a(i, c + 1)
  Next c
Next i
Cells(1, lc + 3).Resize(UBound(b, 1), UBound(b, 2)) = b
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.


If you want the results to overwrite the original raw data, then let me know.
 
Upvote 0
I have a similar problem I’m hoping you could help with. I have data in the following format:


[TABLE="width: 184"]
<tbody>[TR]
[TD="width: 103, bgcolor: transparent"]
Name/Address​
[/TD]
[TD="width: 143, bgcolor: transparent"]
Phone/E-Mail​
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] John Smith
[/TD]
[TD="width: 143, bgcolor: transparent"] 111-111-1111
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] 1 Any Street
[/TD]
[TD="width: 143, bgcolor: transparent"] 111-111-1112
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] Anytown, NY 11111
[/TD]
[TD="width: 143, bgcolor: transparent"] john@anyemail.com
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] Mary Smith
[/TD]
[TD="width: 143, bgcolor: transparent"] 222-222-2222
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] 2 Any Street
[/TD]
[TD="width: 143, bgcolor: transparent"] 222-222-2222
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] Anytown, NJ 22222
[/TD]
[TD="width: 143, bgcolor: transparent"] mary@anyemail.com
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] Bob Smith
[/TD]
[TD="width: 143, bgcolor: transparent"] 333-333-3333
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] 3 Any Street
[/TD]
[TD="width: 143, bgcolor: transparent"] 333-333-3333
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"] Anytown, CA 33333
[/TD]
[TD="width: 143, bgcolor: transparent"] bob@anyemail.com
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


(John Smith is in cell B3, john@anyemail.com is in cell C5)

I need to get the data in the following format of a new sheet:

[TABLE="width: 336"]
<tbody>[TR]
[TD="width: 31, bgcolor: transparent"]
[/TD]
[TD="width: 49, bgcolor: transparent"] Name
[/TD]
[TD="width: 52, bgcolor: transparent"] Addr1
[/TD]
[TD="width: 63, bgcolor: transparent"] Addr2
[/TD]
[TD="width: 55, bgcolor: transparent"] Phone1
[/TD]
[TD="width: 55, bgcolor: transparent"] Phone2
[/TD]
[TD="width: 143, bgcolor: transparent"] Email
[/TD]
[/TR]
[TR]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 49, bgcolor: transparent"] John Smith
[/TD]
[TD="width: 52, bgcolor: transparent"] 1 Any Street
[/TD]
[TD="width: 63, bgcolor: transparent"] Anytown, NY 11111
[/TD]
[TD="width: 55, bgcolor: transparent"] 111-111-1111
[/TD]
[TD="width: 55, bgcolor: transparent"] 111-111-1112
[/TD]
[TD="width: 143, bgcolor: transparent"] john@anyemail.com
[/TD]
[/TR]
[TR]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 49, bgcolor: transparent"] Mary Smith
[/TD]
[TD="width: 52, bgcolor: transparent"] 2 Any Street
[/TD]
[TD="width: 63, bgcolor: transparent"] Anytown, NJ 22222
[/TD]
[TD="width: 55, bgcolor: transparent"] 222-222-2222
[/TD]
[TD="width: 55, bgcolor: transparent"] 222-222-2222
[/TD]
[TD="width: 143, bgcolor: transparent"] mary@anyemail.com
[/TD]
[/TR]
[TR]
[TD="width: 31, bgcolor: transparent"][/TD]
[TD="width: 49, bgcolor: transparent"] Bob Smith
[/TD]
[TD="width: 52, bgcolor: transparent"] 3 Any Street
[/TD]
[TD="width: 63, bgcolor: transparent"] Anytown, CA 33333
[/TD]
[TD="width: 55, bgcolor: transparent"] 333-333-3333
[/TD]
[TD="width: 55, bgcolor: transparent"] 333-333-3333
[/TD]
[TD="width: 143, bgcolor: transparent"] bob@anyemail.com
[/TD]
[/TR]
</tbody>[/TABLE]

(John Smith is in cell B2)

The initial data could be hundreds of rows that I’m currently manually rearranging, and as you can imagine, is very time consuming. The initial data is always broken up into sets of 3 rows and 2 columns per record. Any assistance you could provide would be greatly appreciated.
 
Upvote 0
JCD1078,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


Your dataset is not similar to/the same as kabeldirect's.


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:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
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.


After you do the above, send me a Private Message, with a link to this thread, and, I will have a look.
 
Upvote 0
Sorry, I've never posted to these boards before. I'll create a new post. I can't install those programs at work so I'll do it from home later tonight. Thank you for the response.
 
Upvote 0
hello, i have the exact same problem, however the length of the row is up to 21 cells.

[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

is it possible to modify the macro from 7 cells width up to 21 cells? i tried it myself, but im not familiar with the structure of the formulas.
the "length" of the row varies, although its not that important, because i can remove the empty rows later
thank you, hope it makes sense
 
Upvote 0
petrdusek,

Welcome to the MrExcel forum.

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

2. Are you using a PC or a Mac?


Here is a macro solution for you to consider, that uses two arrays in memory, and, is based on your flat text raw data structure.

Because of the variable number of cells in each row, you will not be able to add additional raw data after the macro, and, run the macro again successfully.

You can change the raw data worksheet name in the macro.

Sample raw data, and, results (the results will begin in the third column to the right of the last used column of your raw data):


Excel 2007
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1A2110412338440341353457.52694722A21
2B0.5143194213444552724A104
3A123
4A384
5A403
6A413
7A534
8A57.52
9A694
10A722
11B0.51
12B43
13B194
14B213
15B444
16B552
17B724
18
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
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_V2()
' hiker95, 01/29/2016
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 = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  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
      If Not a(i, c) = vbEmpty And Not a(i, c + 1) = vbEmpty Then
        j = j + 1
        o(j, 1) = a(i, 1)
        o(j, 2) = a(i, c)
        o(j, 3) = a(i, c + 1)
      End If
    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_V2 macro.
 
Upvote 0
Because of the variable number of cells in each row, you will not be able to add additional raw data after the macro, and, run the macro again successfully.
Click to expand...

petrdusek,

If the above quote is not acceptable, then I will have to see screenshots of the beginning raw data, and, of what the additional NEW raw data will look like.

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
 
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
246
Joe4
Joe4
S
  • Solved
Require the heading date in which column found the first data/value in a specific row.
Replies
4
Views
95
Peter_SSs
Peter_SSs
B
  • Question Question
Even distribution of hours/value across multiple rows
Replies
7
Views
365
BGwayne
B
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
291
Peter_SSs
Peter_SSs
T
  • Solved
Excel formula: how to sum multiple columns on a single criteria
Replies
3
Views
410
TT123
T

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