Here is a great and effective approach to shuffling columns of data.
It will use the Excel HLOOKUP function, and not require any VBA macros
or complicated re-engineering of this Summary worksheet.
As we can see this worksheet contains a summary of financial metrics
of various stocks.
There a number of steps required, before we can shuffle these columns
0:00:35.066,0:00:35.000 Firstly,
we must apply the COLUMNS function in row 8.
Second,
we will employ the RAND() function in row 9.
This will enable the shuffle of dependent data across the columns.
Thirdly,
the combination of VLOOKUP and INDIRECT functions will ensure the flexing of outputs.
The below video link will demonstrate the use of VLOOKUP and INDIRECT further.
Finally, 0:01:25.066,0:01:25.000
the use of HLOOKUP and LARGE functions will guarantee the shuffling of data is dynamic.
Like the third step, with the VLOOKUP and INDIRECT formula,
please be careful to type this formula accurately –
otherwise the formula and shuffle may not work.
This is the vital piece of this exercise.
As we can see,
when we drag or copy across the formula –
the ticker code in row 7 changes.
It is always prudent to perform a spot check of any complex Excel formula;
to ensure the precedent and dependent cells are functioning correctly.
There is one alternative to shuffling of
columns of data.
It involves changing the LARGE with the SMALL function inside the HLOOKUP function
in the fourth step.
As in the following formula in cell C7.
We will quickly verify the HLOOKUP formula is working –
by ensuring each of the 10 ASX stocks are correctly shuffling.
Again if we enter on any of the RAND() formula cells in row 9,
we can see our data columns are seamlessly shuffling.
Perhaps I need to objectively analyse the fundamentals, and so I want to hide the ASX ticker codes in row 7.
In Format Cells – Custom, I will hide these cells thanks to three semicolons.
Now I can objectively analyse the fundamentals of these 10 ASX stocks –
without any bias.
For now I will unhide the ASX codes in row 7, by clearing the Custom Format cells.
Let’s do a couple of checks back to the source worksheets –
by ASX stock.
Firstly,
the FMG or Fortescue tab –
we can see the four qualitative metrics are correct.
We can see the RIO or Rio Tinto "Value" outputs
are correctly referencing the following metrics.
By applying the above 4 steps, we are able to shuffle columns of data; without the use of esoteric VBA macros.