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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1
    Quote from Carim

    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 "Smiley" 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 "Smiley" icon, below, in the bottom right corner :)

    Quote from Carim

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

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1

    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

    :)

    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 "Smiley" icon, below, in the bottom right corner :)

    Love 1
    • Best Answer

    The dates in your data sheet are actually text, so there is no match for the search criteria and n = 0. Since you can't resize a range to 0 rows high, an error occurs. You can avoid the error by adding If n > 0 then to the start of that line, but that will simply mask your problem. You need to fix the source data so that it has actual dates.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Those are still text.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

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

Register Yourself Login