Stack the data into columns into rows

K

klllmmm

New Member
Joined
Nov 14, 2015
Messages
13
I have a large dataset with product related data in different columns for each data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Product A
[/TD]
[TD]Product A
[/TD]
[TD]Product B
[/TD]
[TD]Product B
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Qty
[/TD]
[TD]Value
[/TD]
[TD]Qty
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]50
[/TD]
[TD]1000
[/TD]
[TD]40
[/TD]
[TD]800
[/TD]
[/TR]
[TR]
[TD]02-09-2017
[/TD]
[TD]60
[/TD]
[TD]1200
[/TD]
[TD]20
[/TD]
[TD]400
[/TD]
[/TR]
</tbody>[/TABLE]

I want to make it columner base dataset similar below table.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Product
[/TD]
[TD]Qty
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]Product A
[/TD]
[TD]50
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]Product B
[/TD]
[TD]40
[/TD]
[TD]800
[/TD]
[/TR]
[TR]
[TD]02-09-2017
[/TD]
[TD]Product A
[/TD]
[TD]60
[/TD]
[TD]1200
[/TD]
[/TR]
[TR]
[TD]02-09-2017
[/TD]
[TD]Product B
[/TD]
[TD]20
[/TD]
[TD]400
[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to do this.
Thanks for your time & efforts.
Regards,
Klllmmm
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
You can run this code on a copy of your data

Code: 
Sub CONVERTROWSTOCOL_Oeldere_revisted_new()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet

'check if sheet "output" already exist

Const strSheetName As String = "Output"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

'set the data
                 

Set mr = Sheets("sheet1")                                  'this is the name of the source sheet
 
Set ms = Sheets("Output")                                       'this is the name of the destiny sheet

col = 2
col2 = 3
'End set the data

    With ms
     .UsedRange.ClearContents
     .Range("A1:D1").Value = Array("Issue", "Date", "Qty", "value")
    End With
    
    rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row
    
    
    With mr
          rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row
          For i = 3 To rsht1
                Do While .Cells(1, col).Value <> "" 'And .Cells(I, col).Value <> ""
                rsht2 = rsht2 + 1
               
                ms.Range("A" & rsht2).Value = .Range("A" & i).Value
                
                ms.Range("B" & rsht2).Value = .Cells(1, col).Value
                
                ms.Range("C" & rsht2).Value = .Cells(i, col).Value
                
                ms.Range("D" & rsht2).Value = .Cells(i, col2).Value
                
         
                col = col + 2
                col2 = col2 + 2
            Loop
            col = 2
            col2 = 3
        Next
    End With
    
  With ms
  
  


  
    .Columns("A:Z").EntireColumn.AutoFit
    
    End With
    
End Sub
 
Upvote 0
klllmmm said:
I have a large dataset ..
Click to expand...
If the data is very large, then you may find this faster.
For the moment, this code puts the results off to the right of the original data, but could be put wherever you want.
I have also made some assumptions - see under the first screen shot below.
Do any testing on a copy of your workbook.

Code: 
Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 4)
  For i = 3 To UBound(a, 1)
    For j = 2 To UBound(a, 2) Step 2
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(1, j): b(k, 3) = a(i, j): b(k, 4) = a(i, j + 1)
    Next j
  Next i
  With Range("A1").Offset(, UBound(a, 2) + 1).Resize(, 4)
    .Offset(1).Resize(k).Value = b
    .Value = Array("Date", "Product", "Qty", "Value")
    .EntireColumn.AutoFit
  End With
End Sub

Original data in columns A:E, results of the above code in columns G:J


Excel 2016 (Windows) 32 bit
ABCDEFGHIJ
1DateProduct AProduct AProduct BProduct BDateProductQtyValue
2QtyValueQtyValue1/09/2017Product A501000
31/09/2017501000408001/09/2017Product B40800
42/09/2017601200204002/09/2017Product A601200
52/09/2017Product B20400
6
Sheet1



Assumptions:
1. There are no completely empty rows or columns within the data.
2. Original headings occupy 2 rows as shown above.
3. All rows of data occupy the same number of columns. That is, you don't have 'staggered' data like the sample below. (If you do, post back with details and the code can be modified.)


Excel 2016 (Windows) 32 bit
ABCDEFG
1DateProduct AProduct AProduct BProduct BProduct CProduct C
2QtyValueQtyValueQtyValue
31/09/201750100040800
42/09/201710800
53/09/20176012002040080200
Sheet2
 
Upvote 0
Peter_SSs said:
If the data is very large, then you may find this faster.
For the moment, this code puts the results off to the right of the original data, but could be put wherever you want.
I have also made some assumptions - see under the first screen shot below.
Do any testing on a copy of your workbook.

Code: 
Sub Rearrange()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 4)
  For i = 3 To UBound(a, 1)
    For j = 2 To UBound(a, 2) Step 2
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(1, j): b(k, 3) = a(i, j): b(k, 4) = a(i, j + 1)
    Next j
  Next i
  With Range("A1").Offset(, UBound(a, 2) + 1).Resize(, 4)
    .Offset(1).Resize(k).Value = b
    .Value = Array("Date", "Product", "Qty", "Value")
    .EntireColumn.AutoFit
  End With
End Sub

Original data in columns A:E, results of the above code in columns G:J

Excel 2016 (Windows) 32 bit
ABCDEFGHIJ
Product
Product A
Product B
Product A
Product B

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Date[/TD]
[TD="align: right"]Product A[/TD]
[TD="align: right"]Product A[/TD]
[TD="align: right"]Product B[/TD]
[TD="align: right"]Product B[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Date[/TD]

[TD="align: right"]Qty[/TD]
[TD="align: right"]Value[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Qty[/TD]
[TD="align: right"]Value[/TD]
[TD="align: right"]Qty[/TD]
[TD="align: right"]Value[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/09/2017[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"]1000[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/09/2017[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/09/2017[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]800[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/09/2017[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2/09/2017[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]1200[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2/09/2017[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]400[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​




Assumptions:
1. There are no completely empty rows or columns within the data.
2. Original headings occupy 2 rows as shown above.
3. All rows of data occupy the same number of columns. That is, you don't have 'staggered' data like the sample below. (If you do, post back with details and the code can be modified.)

Excel 2016 (Windows) 32 bit
ABCDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Date[/TD]
[TD="align: right"]Product A[/TD]
[TD="align: right"]Product A[/TD]
[TD="align: right"]Product B[/TD]
[TD="align: right"]Product B[/TD]
[TD="align: right"]Product C[/TD]
[TD="align: right"]Product C[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Qty[/TD]
[TD="align: right"]Value[/TD]
[TD="align: right"]Qty[/TD]
[TD="align: right"]Value[/TD]
[TD="align: right"]Qty[/TD]
[TD="align: right"]Value[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1/09/2017[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2/09/2017[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3/09/2017[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]200[/TD]

</tbody>
Sheet2
​
Click to expand...

Thank you very much. Above code work perfectly for my sample data.

However i found that my real data is some what different to sample.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]CustomerCode
[/TD]
[TD]CustomerName
[/TD]
[TD]Product A
[/TD]
[TD]Product A
[/TD]
[TD]Product B
[/TD]
[TD]Product B
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Qty
[/TD]
[TD]Value
[/TD]
[TD]Qty
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]C100
[/TD]
[TD]Customer A
[/TD]
[TD]50
[/TD]
[TD]1000
[/TD]
[TD]40
[/TD]
[TD]800
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]C200
[/TD]
[TD]Customer B
[/TD]
[TD]70
[/TD]
[TD]1400
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02-09-2017
[/TD]
[TD]C100
[/TD]
[TD]Customer A
[/TD]
[TD]60
[/TD]
[TD]1200
[/TD]
[TD]20
[/TD]
[TD]400
[/TD]
[/TR]
</tbody>[/TABLE]

There can be product quantities & Values with no values.
I need consider customer code & names also when stacking the data.

I'm looking for a output similar to this. I prefer to get the output into a new sheet as there are many product data columns.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]CustomerCode
[/TD]
[TD]CustomerName
[/TD]
[TD]Product
[/TD]
[TD]Qty
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]C100
[/TD]
[TD]Customer A
[/TD]
[TD]Product A
[/TD]
[TD]50
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]C100
[/TD]
[TD]Customer A
[/TD]
[TD]Product B
[/TD]
[TD]40
[/TD]
[TD]800
[/TD]
[/TR]
[TR]
[TD]01-09-2017
[/TD]
[TD]C200
[/TD]
[TD]Customer B
[/TD]
[TD]Product A
[/TD]
[TD]50
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]02-09-2017
[/TD]
[TD]C100
[/TD]
[TD]Customer A
[/TD]
[TD]Product A
[/TD]
[TD]60
[/TD]
[TD]1200
[/TD]
[/TR]
[TR]
[TD]02-09-2017
[/TD]
[TD]C100[/TD]
[TD]Customer A
[/TD]
[TD]Product B
[/TD]
[TD]20
[/TD]
[TD]400
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much for the effort you make..
 
Upvote 0
Best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only - like I have done below.

klllmmm said:
There can be product quantities & Values with no values.
Click to expand...
Ah, yes, I did mention that possibility/problem in my assumptions (3) above. :)

Try this version.

Code: 
Sub Rearrange_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 6)
  For i = 3 To UBound(a, 1)
    For j = 4 To UBound(a, 2) Step 2
      If Len(a(i, j)) > 0 Then
      k = k + 1
        b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = a(i, 3)
        b(k, 4) = a(1, j): b(k, 5) = a(i, j): b(k, 6) = a(i, j + 1)
      End If
    Next j
  Next i
  Sheets.Add After:=ActiveSheet
  With Range("A1").Resize(, UBound(b, 2))
    .Offset(1).Resize(k).Value = b
    .Value = Array("Date", "Customer Code", "Customer Name", "Product", "Qty", "Value")
    .EntireColumn.AutoFit
  End With
End Sub
 
Upvote 0
Thank you very much, the code works perfectly.:cool:

What changes i should make to the VBA code, if i have another value "Doc No", that also need to be stacked.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]CustomerCode[/TD]
[TD]CustomerName[/TD]
[TD]Product A[/TD]
[TD]Product A[/TD]
[TD]Product A[/TD]
[TD]Product B[/TD]
[TD]Product B[/TD]
[TD]Product B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Qty[/TD]
[TD]Value[/TD]
[TD]Doc No[/TD]
[TD]Qty[/TD]
[TD]Value[/TD]
[TD]Doc No[/TD]
[/TR]
[TR]
[TD]01-09-2017[/TD]
[TD]C100[/TD]
[TD]Customer A[/TD]
[TD]50[/TD]
[TD]1000[/TD]
[TD]1[/TD]
[TD]40[/TD]
[TD]800[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]01-09-2017[/TD]
[TD]C200[/TD]
[TD]Customer B[/TD]
[TD]70[/TD]
[TD]1400[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02-09-2017[/TD]
[TD]C100[/TD]
[TD]Customer A[/TD]
[TD]60[/TD]
[TD]1200[/TD]
[TD]4[/TD]
[TD]20[/TD]
[TD]400[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Below is my expected table.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]CustomerCode[/TD]
[TD]CustomerName[/TD]
[TD]Product[/TD]
[TD]Qty[/TD]
[TD]Value[/TD]
[TD]Doc No[/TD]
[/TR]
[TR]
[TD]01-09-2017[/TD]
[TD]C100[/TD]
[TD]Customer A[/TD]
[TD]Product A[/TD]
[TD]50[/TD]
[TD]1000[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]01-09-2017[/TD]
[TD]C100[/TD]
[TD]Customer A[/TD]
[TD]Product B[/TD]
[TD]40[/TD]
[TD]800[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]01-09-2017[/TD]
[TD]C200[/TD]
[TD]Customer B[/TD]
[TD]Product A[/TD]
[TD]70[/TD]
[TD]1400[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]02-09-2017[/TD]
[TD]C100[/TD]
[TD]Customer A[/TD]
[TD]Product A[/TD]
[TD]60[/TD]
[TD]1200[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]02-09-2017[/TD]
[TD]C100[/TD]
[TD]Customer A[/TD]
[TD]Product B[/TD]
[TD]20[/TD]
[TD]400[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Highly appreciate if you can further help me on this.

Regards,
klllmmm
 
Upvote 0
klllmmm said:
What changes i should make to the VBA code, if i have another value "Doc No", that also need to be stacked.
Click to expand...
ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 7)
For j = 4 To UBound(a, 2) Step 3
b(k, 4) = a(1, j): b(k, 5) = a(i, j): b(k, 6) = a(i, j + 1): b(k, 7) = a(i, j + 2)

You will also need to change the line near the end that enters the headings on the new sheet. I'll leave you to make that change.
 
Upvote 0
You did not reply on my solution.

You expand your question by adding new conditions.

It would be better if you show your (total) question, so the code don't need to change again for each question.

Having said that, you have to try to understand the code (although that is somethings difficult) so you can change the given code for other simular questions.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Stacked Clustered Column Chart
Replies
0
Views
235
johnnypp
J
B
  • Solved
Group number of purchased SKUs by counting customer
2
Replies
16
Views
539
shaowu459
shaowu459
CubaRJ
  • Question Question
Calculate Overtime with 2 codes for the same date
Replies
0
Views
179
CubaRJ
CubaRJ
R
  • Solved
transpose data with same value in column A by date order
Replies
5
Views
389
Fluff
Fluff
E
  • Solved
Find a value matching a range upon several criteria
Replies
2
Views
156
ED38
E

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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