Stack data in multiple columns into one but carry over row headers

ezpzspreadsheets

New Member
Joined
Dec 17, 2017
Messages
2
I have an array of a sales report that was a .csv. I ran the text to columns and am left with this. I named the data set A2:F6 as "MYDATA"

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane
[/TD]
[TD]Shirt[/TD]
[TD]Leggings[/TD]
[TD]Dress[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Edith[/TD]
[TD]Leggings[/TD]
[TD]Skirt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Cora[/TD]
[TD]Leggings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sallie[/TD]
[TD]Skirt[/TD]
[TD]Dress[/TD]
[TD]Dress[/TD]
[TD]Leggings[/TD]
[TD]Shirt[/TD]
[/TR]
</tbody>[/TABLE]

I want the data to look like this

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Item[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane[/TD]
[TD]Shirt[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jane[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jane[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Edith[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Edith[/TD]
[TD]Skirt[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Cora[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Sallie[/TD]
[TD]Skirt[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Sallie[/TD]
[TD]Dress[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Sallie[/TD]
[TD]Leggings[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Sallie[/TD]
[TD]Shirt[/TD]
[/TR]
</tbody>[/TABLE]

I've tried

INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

to get the ITEM in one column, but am not sure how to carry over the Header information (names) in column A. Thanks in advance.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Here's a macro :
Code:
Sub v()
Dim nme As Range, cel As Range, rw%, itm As Range
Set nme = Range([A2], Cells(Rows.Count, "A").End(xlUp))
Application.ScreenUpdating = False
For Each cel In nme
    rw = Cells(Rows.Count, "A").End(xlUp)(2).Row
    Set itm = Range(Cells(cel.Row, "B"), Cells(cel.Row, Columns.Count).End(xlToLeft))
    cel.Copy Cells(rw, "A").Resize(itm.Cells.Count)
    Cells(rw, "B").Resize(itm.Cells.Count) = Application.Transpose(itm)
Next
Range([C1], Cells(1, Columns.Count).End(xlToLeft)).ClearContents
nme.EntireRow.Delete
Application.ScreenUpdating = True
End Sub
 
Upvote 0
ezpzspreadsheets,

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, that uses two arrays in memory, and, should be fast.

The original raw data in Range(A1:F6) will be removed, and, the results will be written to Range(A1:B13).

If you want the results to be written to the right of he raw data, then I can adjust 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 ReorganizeData()
' hiker95, 12/17/2017, ME1035652
Dim a As Variant, lr As Long, lc As Long, n As Long, r As Long, c As Long
Dim o As Variant, j 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))
  n = Application.CountA(.Range(.Cells(2, 2), .Cells(lr, lc))) + 1
  ReDim o(1 To n, 1 To 2)
  j = j + 1: o(j, 1) = "Name": o(j, 2) = "Item"
  For r = 2 To lr
    For c = 2 To lc
      If Not a(r, c) = vbEmpty Then
        j = j + 1: o(j, 1) = a(r, 1)
        o(j, 2) = a(r, c)
      End If
    Next c
  Next r
  .Range(.Cells(1, 1), .Cells(lr, lc)).ClearContents
  .Cells(1, 1).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 ReorganizeData macro.
 
Last edited:
Upvote 0
ezpzspreadsheets,

Here is a macro that will write the results to the third column to the right of the last used raw data column.

Code:
Sub ReorganizeData_V2()
Dim a As Variant, lr As Long, lc As Long, n As Long, r As Long, c As Long
Dim o As Variant, j 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))
  n = Application.CountA(.Range(.Cells(2, 2), .Cells(lr, lc))) + 1
  ReDim o(1 To n, 1 To 2)
  j = j + 1: o(j, 1) = "Name": o(j, 2) = "Item"
  For r = 2 To lr
    For c = 2 To lc
      If Not a(r, c) = vbEmpty Then
        j = j + 1: o(j, 1) = a(r, 1)
        o(j, 2) = a(r, c)
      End If
    Next c
  Next r
  .Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2)) = o
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you!

Question... what would be the modification to make if there were multiple "header" rows to carry over... example: Name in column A, Date in Column B, Order Number in Column C, then Items in columns D through H.
 
Upvote 0
Question... what would be the modification to make if there were multiple "header" rows to carry over... example: Name in column A, Date in Column B, Order Number in Column C, then Items in columns D through H.

ezpzspreadsheets,

Can we have two more flat text displays (based on the above quote), like your original reply #1?
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

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