Forum Discussion
Unpivot Monthly Data with a Formula
Note: I know how to unpivot data using PowerQuery and vba. What I'm interested in with this exercise is using a formula to unpivot monthly data. I'm interested in using some of the newest functions...
Thank you for sharing. The more examples I see with MAKEARRAY, it seems to confirm the function is not be used for re-shaping data. Its best use is in generating random data (I'd argue there are other simpler options available to doing the same task).
Re: calculation times - I have a working knowledge of VBA but would much rather do a task at the sheet level if possible (Especially with the wealth of new functions being released). The way I was told to write code was to minimize 'touching the sheet' (e.g. write data to cells rather than copy/paste, avoid .select, etc.). I'd like to understand where MAKEARRAY's calculation time is coming from. My guess is the use of INDEX and having to 'touch the sheet' more than the HSTACK solution.
I agree that INDEX should take many more cycles than HSTACK. Basically you are evaluating/executing on every element while HSTACK presumably acts on the entire array(s). I presume you could 'test' this somewhat by comparing a 100,000x2 grid compared to a 2x100,000 grid since the former should show significant improvement using HSTACK but MAY (depending on the coding) show much less improvement on the latter.