Stack the data into columns into rows

klllmmm

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

Date
Product A
Product A
Product B
Product B
Qty
Value
Qty
Value
01-09-2017
50
1000
40
800
02-09-2017
60
1200
20
400

<tbody>
</tbody>

I want to make it columner base dataset similar below table.
Date
Product
Qty
Value
01-09-2017
Product A
50
1000
01-09-2017
Product B
40
800
02-09-2017
Product A
60
1200
02-09-2017
Product B
20
400

<tbody>
</tbody>

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

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,316
Office Version
365
Platform
Windows
I have a large dataset ..
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

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255, 255, 255);border: 1px solid;border-collapse: collapse; border-color: rgb(182, 170, 166)"><colgroup><col width="25px" style="background-color: rgb(240, 224, 224)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(240, 224, 224);text-align: center;color: rgb(32, 17, 22)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1</td><td style="text-align: right;;">Date</td><td style="text-align: right;;">Product A</td><td style="text-align: right;;">Product A</td><td style="text-align: right;;">Product B</td><td style="text-align: right;;">Product B</td><td style="text-align: right;;"></td><td style="text-align: right;;">Date</td><td style=";">Product</td><td style="text-align: right;;">Qty</td><td style="text-align: right;;">Value</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">Qty</td><td style="text-align: right;;">Value</td><td style="text-align: right;;">Qty</td><td style="text-align: right;;">Value</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/09/2017</td><td style=";">Product A</td><td style="text-align: right;;">50</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">3</td><td style="text-align: right;;">1/09/2017</td><td style="text-align: right;;">50</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">40</td><td style="text-align: right;;">800</td><td style="text-align: right;;"></td><td style="text-align: right;;">1/09/2017</td><td style=";">Product B</td><td style="text-align: right;;">40</td><td style="text-align: right;;">800</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">4</td><td style="text-align: right;;">2/09/2017</td><td style="text-align: right;;">60</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">20</td><td style="text-align: right;;">400</td><td style="text-align: right;;"></td><td style="text-align: right;;">2/09/2017</td><td style=";">Product A</td><td style="text-align: right;;">60</td><td style="text-align: right;;">1200</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2/09/2017</td><td style=";">Product B</td><td style="text-align: right;;">20</td><td style="text-align: right;;">400</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(182, 170, 166);border-top:none;text-align: center;background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">Sheet1</p><br /><br />


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

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255, 255, 255);border: 1px solid;border-collapse: collapse; border-color: rgb(182, 170, 166)"><colgroup><col width="25px" style="background-color: rgb(240, 224, 224)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(240, 224, 224);text-align: center;color: rgb(32, 17, 22)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(32, 17, 22);text-align: center;">1</td><td style="text-align: right;;">Date</td><td style="text-align: right;;">Product A</td><td style="text-align: right;;">Product A</td><td style="text-align: right;;">Product B</td><td style="text-align: right;;">Product B</td><td style="text-align: right;;">Product C</td><td style="text-align: right;;">Product C</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">Qty</td><td style="text-align: right;;">Value</td><td style="text-align: right;;">Qty</td><td style="text-align: right;;">Value</td><td style="text-align: right;;">Qty</td><td style="text-align: right;;">Value</td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">3</td><td style="text-align: right;;">1/09/2017</td><td style="text-align: right;;">50</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">40</td><td style="text-align: right;;">800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">4</td><td style="text-align: right;;">2/09/2017</td><td style="text-align: right;;">10</td><td style="text-align: right;;">800</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(32, 17, 22);text-align: center;">5</td><td style="text-align: right;;">3/09/2017</td><td style="text-align: right;;">60</td><td style="text-align: right;;">1200</td><td style="text-align: right;;">20</td><td style="text-align: right;;">400</td><td style="text-align: right;;">80</td><td style="text-align: right;;">200</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(182, 170, 166);border-top:none;text-align: center;background-color: rgb(240, 224, 224);color: rgb(32, 17, 22)">Sheet2</p><br /><br />
 

klllmmm

New Member
Joined
Nov 14, 2015
Messages
13
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
1DateProduct AProduct AProduct BProduct BProduct CProduct C
2QtyValueQtyValueQtyValue
31/09/201750100040800
42/09/201710800
53/09/20176012002040080200

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
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.


Date
CustomerCode
CustomerName
Product A
Product A
Product B
Product B
Qty
Value
Qty
Value
01-09-2017
C100
Customer A
50
1000
40
800
01-09-2017
C200
Customer B
70
1400
02-09-2017
C100
Customer A
60
1200
20
400

<tbody>
</tbody>

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.

Date
CustomerCode
CustomerName
Product
Qty
Value
01-09-2017
C100
Customer A
Product A
50
1000
01-09-2017
C100
Customer A
Product B
40
800
01-09-2017
C200
Customer B
Product A
50
1000
02-09-2017
C100
Customer A
Product A
60
1200
02-09-2017
C100Customer A
Product B
20
400

<tbody>
</tbody>

Thank you very much for the effort you make..
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,316
Office Version
365
Platform
Windows
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.

There can be product quantities & Values with no values.
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
 

klllmmm

New Member
Joined
Nov 14, 2015
Messages
13
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.

DateCustomerCodeCustomerNameProduct AProduct AProduct AProduct BProduct BProduct B
QtyValueDoc NoQtyValueDoc No
01-09-2017C100Customer A5010001408002
01-09-2017C200Customer B7014003
02-09-2017C100Customer A6012004204005

<tbody>
</tbody>

Below is my expected table.
DateCustomerCodeCustomerNameProductQtyValueDoc No
01-09-2017C100Customer AProduct A5010001
01-09-2017C100Customer AProduct B408002
01-09-2017C200Customer BProduct A7014003
02-09-2017C100Customer AProduct A6012004
02-09-2017C100Customer AProduct B204005

<tbody>
</tbody>

Highly appreciate if you can further help me on this.

Regards,
klllmmm
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,316
Office Version
365
Platform
Windows
What changes i should make to the VBA code, if i have another value "Doc No", that also need to be stacked.
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.
 

Oeldere

Well-known Member
Joined
Dec 29, 2011
Messages
2,209
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.
 

Forum statistics

Threads
1,082,573
Messages
5,366,378
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top