How To Convert Horizontal List To Vertical List?

W

wisekhan

New Member
Joined
Dec 13, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Hello,
I have a table that contained, below, I want to transform table like this, is that possible to make with vba or using formula? Thanks for your help.

Original table below:

Line_NameStock_code
22.06.2022​
23.06.2022​
24.06.2022​
K9-CMF1 TUBE GUIDE9813924080
1725​
1976​
1579​
K9-CMF1 TUBE GUIDE9813924280
1076​
1887​
1782​
K9-CMF1 TUBE GUIDE9813924580
1062​
1594​
1662​
BJA TUBE GUIDE365373502R
1292​
1039​
1842​
BJA TUBE GUIDE365368798R
1017​
1546​
1916​
PSA CMP TUBE GUIDE9824836980
1512​
1389​
1576​

Transformed table sample below

Stock_codeLine_NameQuantityDate
9813924080​
K9-CMF1 TUBE GUIDE
1725​
22.06.2022​
9813924080​
K9-CMF1 TUBE GUIDE
1976​
23.06.2022​
9813924080​
K9-CMF1 TUBE GUIDE
1579​
24.06.2022​
9813924280​
K9-CMF1 TUBE GUIDE
1076​
22.06.2022​
9813924280​
K9-CMF1 TUBE GUIDE
1887​
23.06.2022​
9813924280​
K9-CMF1 TUBE GUIDE
1782​
24.06.2022​
9813924580​
K9-CMF1 TUBE GUIDE
1062​
22.06.2022​
9813924580​
K9-CMF1 TUBE GUIDE
1594​
23.06.2022​
9813924580​
K9-CMF1 TUBE GUIDE
1662​
24.06.2022​
365373502RBJA TUBE GUIDE
1292​
22.06.2022​
365373502RBJA TUBE GUIDE
1039​
23.06.2022​
365373502RBJA TUBE GUIDE
1842​
24.06.2022​
365368798RBJA TUBE GUIDE
1017​
22.06.2022​
365368798RBJA TUBE GUIDE
1546​
23.06.2022​
365368798RBJA TUBE GUIDE
1916​
24.06.2022​
9824836980PSA CMP TUBE GUIDE
1512​
22.06.2022​
9824836980PSA CMP TUBE GUIDE
1389​
23.06.2022​
9824836980PSA CMP TUBE GUIDE
1576​
24.06.2022​
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hi and welcome to MrExcel!

Your data on sheet1, starting in cell A1, the results on sheet2 from cell A2 onwards.
Try this:
VBA Code: 
Sub HorizontalToVertical()
  Dim i As Long, j As Long
 
  With Sheets("Sheet1")
    For i = 2 To .Range("A" & Rows.Count).End(3).Row
      For j = 3 To .Cells(1, Columns.Count).End(1).Column
        Sheets("Sheet2").Range("A" & Rows.Count).End(3)(2).Resize(1, 4).Value = Array(.Cells(i, 2), .Cells(i, 1), .Cells(i, j), .Cells(1, j))
      Next
    Next
  End With
End Sub
 
Upvote 0
Welcome to posting at MrExcel!

It is also possible with formulas. The formulas are only required in the top row of the result table as the other values automatically 'spill' down the required rows.

IF you have the TOCOL function (you may not yet have it as it is being gradually rolled out to subscribers)

22 06 29.xlsm
ABCDE
1Line_NameStock_code22.06.202223.06.202224.06.2022
2K9-CMF1 TUBE GUIDE9813924080172519761579
3K9-CMF1 TUBE GUIDE9813924280107618871782
4K9-CMF1 TUBE GUIDE9813924580106215941662
5BJA TUBE GUIDE365373502R129210391842
6BJA TUBE GUIDE365368798R101715461916
7PSA CMP TUBE GUIDE9824836980151213891576
8
9
10Stock_codeLine_NameQuantityDate
119813924080K9-CMF1 TUBE GUIDE172522.06.2022
129813924080K9-CMF1 TUBE GUIDE197623.06.2022
139813924080K9-CMF1 TUBE GUIDE157924.06.2022
149813924280K9-CMF1 TUBE GUIDE107622.06.2022
159813924280K9-CMF1 TUBE GUIDE188723.06.2022
169813924280K9-CMF1 TUBE GUIDE178224.06.2022
179813924580K9-CMF1 TUBE GUIDE106222.06.2022
189813924580K9-CMF1 TUBE GUIDE159423.06.2022
199813924580K9-CMF1 TUBE GUIDE166224.06.2022
20365373502RBJA TUBE GUIDE129222.06.2022
21365373502RBJA TUBE GUIDE103923.06.2022
22365373502RBJA TUBE GUIDE184224.06.2022
23365368798RBJA TUBE GUIDE101722.06.2022
24365368798RBJA TUBE GUIDE154623.06.2022
25365368798RBJA TUBE GUIDE191624.06.2022
269824836980PSA CMP TUBE GUIDE151222.06.2022
279824836980PSA CMP TUBE GUIDE138923.06.2022
289824836980PSA CMP TUBE GUIDE157624.06.2022
Rearrange
Cell Formulas
RangeFormula
A11:A28A11=INDEX(B2:B7,SEQUENCE(ROWS(C11#),,,1/COLUMNS(C1:E1)))
B11:B28B11=INDEX(A2:A7,SEQUENCE(ROWS(C11#),,,1/COLUMNS(C1:E1)))
C11:C28C11=TOCOL(C2:E7)
D11:D28D11=INDEX(C1:E1,MOD(SEQUENCE(ROWS(C11#),,0),3)+1)
Dynamic array formulas.



IF you do not have the TOCOL function yet ..

22 06 29.xlsm
ABCDE
1Line_NameStock_code22.06.202223.06.202224.06.2022
2K9-CMF1 TUBE GUIDE9813924080172519761579
3K9-CMF1 TUBE GUIDE9813924280107618871782
4K9-CMF1 TUBE GUIDE9813924580106215941662
5BJA TUBE GUIDE365373502R129210391842
6BJA TUBE GUIDE365368798R101715461916
7PSA CMP TUBE GUIDE9824836980151213891576
8
9
10Stock_codeLine_NameQuantityDate
119813924080K9-CMF1 TUBE GUIDE172522.06.2022
129813924080K9-CMF1 TUBE GUIDE197623.06.2022
139813924080K9-CMF1 TUBE GUIDE157924.06.2022
149813924280K9-CMF1 TUBE GUIDE107622.06.2022
159813924280K9-CMF1 TUBE GUIDE188723.06.2022
169813924280K9-CMF1 TUBE GUIDE178224.06.2022
179813924580K9-CMF1 TUBE GUIDE106222.06.2022
189813924580K9-CMF1 TUBE GUIDE159423.06.2022
199813924580K9-CMF1 TUBE GUIDE166224.06.2022
20365373502RBJA TUBE GUIDE129222.06.2022
21365373502RBJA TUBE GUIDE103923.06.2022
22365373502RBJA TUBE GUIDE184224.06.2022
23365368798RBJA TUBE GUIDE101722.06.2022
24365368798RBJA TUBE GUIDE154623.06.2022
25365368798RBJA TUBE GUIDE191624.06.2022
269824836980PSA CMP TUBE GUIDE151222.06.2022
279824836980PSA CMP TUBE GUIDE138923.06.2022
289824836980PSA CMP TUBE GUIDE157624.06.2022
Rearrange (2)
Cell Formulas
RangeFormula
A11:A28A11=INDEX(B2:B7,SEQUENCE(ROWS(C11#),,,1/COLUMNS(C1:E1)))
B11:B28B11=INDEX(A2:A7,SEQUENCE(ROWS(C11#),,,1/COLUMNS(C1:E1)))
C11:C28C11=LET(rng,C2:E7,cols,COLUMNS(rng),rws,ROWS(rng)*cols,INDEX(C2:E7,SEQUENCE(rws,,,1/cols),MOD(SEQUENCE(rws,,0),cols)+1))
D11:D28D11=INDEX(C1:E1,MOD(SEQUENCE(ROWS(C11#),,0),3)+1)
Dynamic array formulas.
 
Upvote 0
Hi,
Thank you very much for your solutions and help, All of them , it is works perfectly. 👏
 
Upvote 0
You're welcome. Glad we could help. Thanks for the follow-up. :)
 
Upvote 0
An alternative is to unpivot your data with Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Line_Name", "Stock_code"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
R
  • Question Question
Help!
Replies
3
Views
2K
63falcondude
63falcondude

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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