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!

 

[kabeldirect]

Another sample:

A	40	1,35	80	0,99	150	0,72
B	9	9,07	18	8,23	40	7,48
A	40	1,35
A	80	0,99
A	150	0,72
B	9	9,07
B	18	8,23
B	40	7,48

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

 

[hiker95]

kabeldirect,

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

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	40	1,35	80	0,99	150	0,72
2	B	9	9,07	18	8,23	40	7,48
3
4
5
6
7
8
9
10
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	40	1,35	80	0,99	150	0,72			A	40	1,35
2	B	9	9,07	18	8,23	40	7,48			A	80	0,99
3										A	150	0,72
4										B	9	9,07
5										B	18	8,23
6										B	40	7,48
7
8
9
10
Sheet1

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

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	40	1,35	80	0,99	150	0,72			A	40	1,35
2	B	9	9,07	18	8,23	40	7,48			A	80	0,99
3	C	150	0,72	40	1,35	18	8,23			A	150	0,72
4										B	9	9,07
5										B	18	8,23
6										B	40	7,48
7
8
9
10
Sheet1

And, run the macro again:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
1	A	40	1,35	80	0,99	150	0,72			A	40	1,35
2	B	9	9,07	18	8,23	40	7,48			A	80	0,99
3	C	150	0,72	40	1,35	18	8,23			A	150	0,72
4										B	9	9,07
5										B	18	8,23
6										B	40	7,48
7										C	150	0,72
8										C	40	1,35
9										C	18	8,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.

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.

 

[JCD1078]

I have a similar problem I’m hoping you could help with. I have data in the following format:

Name/Address​	Phone/E-Mail​
John Smith	111-111-1111
1 Any Street	111-111-1112
Anytown, NY 11111	john@anyemail.com
Mary Smith	222-222-2222
2 Any Street	222-222-2222
Anytown, NJ 22222	mary@anyemail.com
Bob Smith	333-333-3333
3 Any Street	333-333-3333
Anytown, CA 33333	bob@anyemail.com

<tbody> </tbody>

(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:

	Name	Addr1	Addr2	Phone1	Phone2	Email
	John Smith	1 Any Street	Anytown, NY 11111	111-111-1111	111-111-1112	john@anyemail.com
	Mary Smith	2 Any Street	Anytown, NJ 22222	222-222-2222	222-222-2222	mary@anyemail.com
	Bob Smith	3 Any Street	Anytown, CA 33333	333-333-3333	333-333-3333	bob@anyemail.com

<tbody> </tbody>

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

 

[hiker95]

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.

 

[JCD1078]

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.

 

[hiker95]

JCD1078,

Thank you for the response.

You are very welcome.

I'll do it from home later tonight.

Thank you.

 

[petrdusek]

hello, i have the exact same problem, however the length of the row is up to 21 cells.

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

<tbody>
</tbody>

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

 

[hiker95]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	A	2	1	10	4	12	3	38	4	40	3	41	3	53	4	57.5	2	69	4	72	2			A	2	1
2	B	0.5	1	4	3	19	4	21	3	44	4	55	2	72	4									A	10	4
3																								A	12	3
4																								A	38	4
5																								A	40	3
6																								A	41	3
7																								A	53	4
8																								A	57.5	2
9																								A	69	4
10																								A	72	2
11																								B	0.5	1
12																								B	4	3
13																								B	19	4
14																								B	21	3
15																								B	44	4
16																								B	55	2
17																								B	72	4
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.

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.

 

[hiker95]

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.

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