Create a new row based on multiple columns

[caudle]

Not sure if the title correctly identifies this but here is what I am looking to do. I have a very large dataset but need it in a different format.

I need to transform this:
[TABLE="width: 295"]
<tbody>[TR]
[TD]name
[/TD]
[TD]app1
[/TD]
[TD]app2
[/TD]
[TD]app3
[/TD]
[/TR]
[TR]
[TD]computer 1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]


To this:
[TABLE="width: 167"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]App
[/TD]
[/TR]
[TR]
[TD]computer 1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]computer 1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]computer 1
[/TD]
[TD]3
[/TD]
[/TR]
</tbody>[/TABLE]
I have found VBA that takes the column and creates a new row but not one that copies the first column/row information.

I appreciate any help you can provide.

 

[hiker95]

caudle,

Welcome to the MrExcel forum.

Here is a macro solution for you to consider that will adjust to the number of raw data rows, and, columns, and, that uses two arrays in memory, and, should be fast.

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

Sample raw data:

Excel 2007
	A	B	C	D	E
1	name	app1	app2	app3
2	computer 1	1	2	3
3
4
5
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E
1	Name	App
2	computer 1	1
3	computer 1	2
4	computer 1	3
5
Sheet1

And, if we change the raw data to this:

Excel 2007
	A	B	C	D	E	F
1	name	app1	app2	app3	app4
2	computer 1	1	2	3	4
3	computer 2	5	6		7
4
5
6
7
8
9
Sheet1

And, run the macro again, we get this:

Excel 2007
	A	B	C	D	E	F
1	Name	App
2	computer 1	1
3	computer 1	2
4	computer 1	3
5	computer 1	4
6	computer 2	5
7	computer 2	6
8	computer 2	7
9
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 TransformData()
' hiker95, 10/25/2016, ME972307
Dim a As Variant, i As Long, c As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  a = .Cells(1).CurrentRegion
  ReDim o(1 To UBound(a, 1) * (UBound(a, 2) - 2), 1 To 2)
  j = j + 1: o(j, 1) = "Name": o(j, 2) = "App"
  For i = 2 To UBound(a, 1)
    For c = 2 To UBound(a, 2)
      If Not a(i, c) = vbEmpty Then
        j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, c)
      End If
    Next c
  Next i
  .Cells(1).CurrentRegion.ClearContents
  .Cells(1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(1).Resize(, UBound(o, 2)).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 TransformData macro.

 

[caudle]

Thank you very much hiker95!! That worked marvelously! I am new to heavy data processing with excel and VBA so I am learning the ropes. This helped me out a lot!

 

[hiker95]

caudle,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[hiker95]

I am new to heavy data processing with excel and VBA so I am learning the ropes. This helped me out a lot!

caudle,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 10/07/2016

http://www.mrexcel.com/forum/excel-questions/970896-checklist.html#post4660105