Equivalent of Power Query - Unpivot Columns in VBA

  • Hello excel magicians,


    Can someone please lend me a helping hand with a macro? I am trying to achieve the same result that I would using PowerQuery(Unpivot Columns) and a little bit more.


    What I am trying to achieve:


    • Unpivot Sheet1 from Column D onward dynamically (there could be more or less than one month added)
    • Unpivot Sheet2 from Column D onward dynamically.
    • Check if month is already in Sheet2, if not append rows from Sheet1. If there's already data for that month, replace it for those particular matching items and append the rest.


    I want to do this without PowerQuery because my table will be ever changing and I don't want to lose the past data referencing that same source.


    I have attached an example of what I am dealing with. I would be grateful if someone could help me with some guidance.


    Thank you,
    Cosmin

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello,


    To Unpivot or Reverse a Pivot Table, there are several readily available solutions ...


    See Debra's excellent site ... http://www.contextures.com/excelunpivotmacro.html


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hey Carim,


    I have already tried that and it's not what I'm looking for. I am tried to adapt it but I was unable to reach my goal. Do you have other suggestions for me?


    Thank you,
    Cosmin

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello again,


    Took me a while to find in my archives this old link ...:wink:


    http://spreadsheetpage.com/index.php/site/tip/creating_a_database_table_from_a_summary_table/


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello Carim,


    That's a really interesting way o doing it. However I have found this solution which works great for unpivoting (if someone else needs it in the future):



    Now that I have the data in the format that I need, would you be so kind to help me with the last part?
    3. Check if month is already in Sheet2, if not append rows from Sheet1. If there's already data for that month, replace it for those particular matching items and append the rest.


    I am having a difficult time trying to figure that out.


    Thank you,
    C

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello Cosmin,


    Thanks a lot for sharing your solution with the Forum ...:smile:


    In order to move forward :


    Say Sheet1 gets "unpivoted" into Sheet3 and Sheet2 gets "unpivoted" into Sheet4 ...


    What are the concrete steps to be taken to generate the Final Sheet ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Good day Carim,


    You are very welcome! The exact next steps after Sheet1 has been unpivoted in Sheet3 and Sheet2 has been unpivoted in Sheet4 are:


    • Copy rows from Sheet3, append them into Sheet4.
    • If items and month (column C, D) from Sheet3 already match the ones in Sheet4, replace the old value from Sheet4 with the values from Sheet3 or replace the whole row altogether.
    • Basically if the rows are matching on all the columns except the value, replace it with the new value, if it doesn't match, append.


    I hope I have explained this well enough. Please let me know if I could be any clearer.


    Thank you,
    C

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello again,


    Just to be 100% sure about what replaces what ... Sheet1 transformed into Sheet3 do contain 'new' and Sheet2 transformed into Sheet4 holds 'old' ....


    Is that right ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello Cosmin,


    Sorry for the delay ...


    Attached is your sample workbook ... :wink:


    Hope this will help ...

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    This is exactly what I was looking for. Thank you for putting everything together.
    I appreciate your help. Do you there's a way to speed up the checking and appending?


    Thanks,
    C

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Glad you could fix your problem ...:wink:


    Thanks ... for your thanks ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello,


    Sorry ... :fatwalk: .... Forgot to add link to speed up all macros ...


    http://datapigtechnologies.com/blog/index.php/ten-things-you-can-do-to-speed-up-your-excel-vba-code/


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hello Carim,


    I have already did that but it's still pretty slow and it freezes everything up when it's running(~15 000rows). Is there a faster approach?


    Thank you,
    C

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Sorry ... :no:... but no other approach ( to my knowledge ...)


    or ... a faster processor :wink: (in a brand new PC) ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: Equivalent of Power Query - Unpivot Columns in VBA


    Hey Carim,


    Thank you. I am already running this on an Alienware. There nothing much I can do about the PC. :)
    It will do for now.


    Have a great day,
    C

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!