Specialized Pivot-like data

  • If we are back to the initial formulas ...


    they are Array Formulas ..


    So instead of the standard Enter key ... you need to use simultaneously the 3 keys : Ctrl Shift Enter


    Hope this will help

    :)

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

  • My very last intuition about your Excel installation... would be related to the specific DLL which is handling, in this particular case, the VBA Collection Object ...


    But, fixing such an issue, is really far beyond my little competence ... sorry ...


    Hopefully, another contributor will specifically help you out regarding this technical problem ...

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

  • My very last intuition about your Excel installation... would be related to the specific DLL which is handling, in this particular case, the VBA Collection Object ...


    But, fixing such an issue, is really far beyond my little competence ... sorry ...


    Hopefully, another contributor will specifically help you out regarding this technical problem ...

    Thank you so much for your time on this, really appreciate it! :thumbup::)

  • Thanks a lot for your Thanks ...:)


    ...AND for the Likes ...:thumbup::thumbup::thumbup:

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

  • Should you find the bottom line of this issue ... would very much appreciate to know the crux of this problem :)

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

  • Should you find the bottom line of this issue ... would very much appreciate to know the crux of this problem :)

    No, I tried it on various systems with no result. Since your formula worked I got my job done. But I was still not able to replicate the formula for the entire range. Your formula is only till row 5653. If my data is more than that, I am running into problems. :)

  • Thanks a lot for the feedback :)


    Whenever I have a moment ... will get back to your macro and look for an alternative method ... ;)


    In the meantime formulas adjusted till 10'000 ...

    Files

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

  • Thanks a lot for the feedback :)


    Whenever I have a moment ... will get back to your macro and look for an alternative method ... ;)


    In the meantime formulas adjusted till 10'000 ...

    Thank you again. Would you be kind enough to alter the formula for me to reflect the entire column?

  • For Array Formulas ... entire columns are really not recommended ... since they heavily impact on the calculation performance ...:cursing:

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

  • Hello,


    Regarding an alternative method to build for your macro ...


    Could you please tell me if you do have access to the ‘ Microsoft Scripting Runtime’ library


    in your menu Alt F11 Tools> References


    or Would you rather use a dedicated worksheet to store all the Unique Values required : Branch, Date, Shift


    Thanks

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

  • I just checked and I didn't have that library selected, did it now.

  • Hello again,


    Attached is test file without Collections and without Dictionary ... to avoid all VBA Objects ...


    As a result, the macro is slower than previous version... but hopefully will run on your computer ...


    Let me have your feedback ...


    Hope this will help

    :)

    Files

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

  • Wow, this works perfect now! Thank you so much. Another big report is coming up for me in two weeks and I was wondering how to manage it, now I can get it done in a few minutes. Really appreciate your time on this and for following up so much. You are awesome!:thumbup:

  • Very glad to hear this patched macro does function properly on your computer :):):)


    Thanks a lot for all your very kind words ... AND for the Like :thumbup:


    Take care;)

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