Reshape table using vba combining multiple columns

[avshi]

Hi all,
I have big table ( thusands of lines, hundreds of columns)

Each line consists of one product, and than few columns of product characters and the other is the price+quantity of the product in different periods.

I want to make my table that way so each line will be consisted of the product (among the characters), the price and quantity and the date (originally- the headers of both price and quantity columns)

I've seen something relativity similar here:
Solved: split multiple column values into multiple rows - Microsoft Power BI Community

But, that was with power bi, and without the heading issue.

Any idea of how can I make it through vba?

Thanks

 

[avshi]

This is what I'm lookin for:
turning this(All the nlanks are filled with price and quantities):

into this:

 

[Akuini]

Hi, avshi
Assuming the header”Product’ is in A2, try this:

---

Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]

[B][COLOR=Royalblue]Sub[/COLOR][/B] a1018066a[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] a [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] c [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] z [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] q [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] y [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] e [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] x [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] va
 
Application.ScreenUpdating [B]=[/B] [B][COLOR=Royalblue]False[/COLOR][/B]
    a [B]=[/B] Range[B]([/B][B][COLOR=brown]"A:A"[/COLOR][/B][B]).[/B]Find[B]([/B][B][COLOR=brown]"*"[/COLOR][/B][B],[/B] SearchOrder[B]:=[/B]xlByRows[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]row
        c [B]=[/B] Rows[B]([/B][B][B][COLOR=crimson]2[/COLOR][/B][/B][B]).[/B]Find[B]([/B][B][COLOR=brown]"*"[/COLOR][/B][B],[/B] SearchOrder[B]:=[/B]xlByColumns[B],[/B] SearchDirection[B]:=[/B]xlPrevious[B]).[/B]Column
            va [B]=[/B] Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][COLOR=brown]"A"[/COLOR][/B][B]),[/B] Cells[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] c[B]))[/B]
                z [B]=[/B] a [B]-[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B]
                    q [B]=[/B] [B]([/B]c [B]-[/B] [B][B][COLOR=crimson]4[/COLOR][/B][/B][B])[/B] [B]/[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B]
                        y [B]=[/B] [B][B][COLOR=crimson]7[/COLOR][/B][/B]
                            e [B]=[/B] [B][B][COLOR=crimson]7[/COLOR][/B][/B]
   
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] q [B]-[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B]
      
       x [B]=[/B] [B][B][COLOR=crimson]3[/COLOR][/B][/B] [B]+[/B] [B]([/B]i [B]*[/B] z[B])[/B]
      
       Range[B]([/B]Cells[B]([/B]x[B],[/B] [B][COLOR=brown]"A"[/COLOR][/B][B]),[/B] Cells[B]([/B]x [B]+[/B] z [B]-[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][COLOR=brown]"D"[/COLOR][/B][B])).[/B]Value [B]=[/B] Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]3[/COLOR][/B][/B][B],[/B] [B][COLOR=brown]"A"[/COLOR][/B][B]),[/B] Cells[B]([/B]a[B],[/B] [B][COLOR=brown]"D"[/COLOR][/B][B])).[/B]Value
       Range[B]([/B]Cells[B]([/B]x[B],[/B] [B][COLOR=brown]"E"[/COLOR][/B][B]),[/B] Cells[B]([/B]x [B]+[/B] z [B]-[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][COLOR=brown]"F"[/COLOR][/B][B])).[/B]Value [B]=[/B] Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]3[/COLOR][/B][/B][B],[/B] y[B]),[/B] Cells[B]([/B]a[B],[/B] y [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])).[/B]Value
       Range[B]([/B]Cells[B]([/B]x[B],[/B] [B][COLOR=brown]"G"[/COLOR][/B][B]),[/B] Cells[B]([/B]x [B]+[/B] z [B]-[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][COLOR=brown]"G"[/COLOR][/B][B])).[/B]Value [B]=[/B] va[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] e[B])[/B]
      
       e [B]=[/B] e [B]+[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B]
       y [B]=[/B] y [B]+[/B] [B][B][COLOR=crimson]2[/COLOR][/B][/B]
      
    [B][COLOR=Royalblue]Next[/COLOR][/B]
  
    Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]3[/COLOR][/B][/B][B],[/B] [B][COLOR=brown]"G"[/COLOR][/B][B]),[/B] Cells[B]([/B]a[B],[/B] [B][COLOR=brown]"G"[/COLOR][/B][B])).[/B]Value [B]=[/B] va[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B],[/B] [B][B][COLOR=crimson]5[/COLOR][/B][/B][B])[/B]
    Range[B]([/B]Cells[B]([/B][B][B][COLOR=crimson]2[/COLOR][/B][/B][B],[/B] [B][COLOR=brown]"H"[/COLOR][/B][B]),[/B] Cells[B]([/B]a[B],[/B] c[B])).[/B]ClearContents
    Rows[B]([/B][B][B][COLOR=crimson]1[/COLOR][/B][/B][B]).[/B]EntireRow.Delete
   
Application.ScreenUpdating [B]=[/B] [B][COLOR=Royalblue]True[/COLOR][/B]
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

---