mirror and/or change justification of a jagged array in VBA or a selected range
This might also fit in data import, formulas & functions, macros, as well as here - but they key reason for this suggestion is to improve performance, so I'm putting it here.
In some cases, raw or processed data results in a jagged array of data, which may start with any justification depending on the data source (top, left, right, bottom - see example below). To change the justification/orientation, it appears the only option is to iterate the array and move the data one piece at a time. When working with (in my current use case) thousands of rows and dozens of columns, that takes more than a few seconds. It seems like there ought to be an easier and faster way to do this.
In addition, a way to just create a mirror image of an array without looping each cell would be helpful (and possibly necessary) to avoid looping each element in the array.
Use case: Start with an employee roster in "Column 1" of the array, look up their manager and stick it in "column 2". Find the manager's manager, and stick it in "column 3". Keep going through management levels until you reach the top of the org. Since each employee may be a different number of levels from the CEO, you end up with a jagged array. To make the data useful for analysis, you really want to start with the CEO on the left, and move down the org in each row, so reversing the array would be helpful.
Even if there was a way to create a mirror of the array to reverse the order of the values, you still have a jagged array, the CEO won't be in column A because she wasn't all in the same column in the original array. If there was a way to just shift all the values to the left (or in other cases, maybe to the right, or up or down depending on what data and how it is structured) I bet that could be optimized by the MS Dev team to be much faster than a VBA loop of the whole array.
Original data, employees on the left, up to CEO Susan:
Betty / Scott / Tina / Carl / Susan / blank / blank / blank
Stephen / Mary / Beth / Susan / blank / blank / blank / blank
Beth / Susan / blank / blank / blank / blank / blank / blank
Mirror of the array at least puts the CEO on the left
blank / blank / blank / Susan / Carl / Tina / Scott / Betty
blank / blank / blank / blank / Susan / Beth / Mary / Stephen
blank / blank / blank / blank / blank / blank / Susan / Beth
but we still have a right-justified array that is hard to work with.
If we could justify the array, e.g. move each value to the left until there are no more leading blanks, the end result would be:
Susan / Carl / Tina / Scott / Betty / blank / blank / blank
Susan / Beth / Mary / Stephen / blank / blank / blank / blank
Susan / Beth / blank / blank / blank / blank / blank / blank
Which is much more useful.:)
For a worksheet (selected range) or table, it would probably be a toolbar command. VBA syntax against an array would have to accommodate different size arrays (1D, 2D, maybe even 3D but that would hurt my brain) and the desired direction of the justification. So if my array above was
Employees(1 to 3, 1 to 10)
ArrayMirror(Employees(2)) maybe would be a command to mirror along the second dimension (the 'row')
ArrayJustify(Employees(2),Left) would then move all the data to the left.
I can imagine some types of jagged arrays where there might be meaningful blanks within the data (not in an employee tree, but other types of arrays) so maybe it would need an optional condition to preserve or remove the blanks when justifying an array. I'm not sure which would be more globally useful, but I'd happily use the optional condition if the public preferred something different than what I would use.