Here is a great and effective approach to shuffle 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.

Strategize FM – Shuffling data with HLOOKUP

Steps to Take

Firstly, we must apply the COLUMNS Excel function in row 8.

Second, we will employ the RAND() Excel 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 following video link will demonstrate the use of VLOOKUP and INDIRECT further – Excel VLOOKUP across multiple worksheets.

Finally, the use of HLOOKUP and LARGE functions will guarantee the shuffling of data is dynamic.

Important Considerations

Like the third step, with the VLOOKUP and INDIRECT formula, please be careful to type this HLOOKUP formula accurately – otherwise the formula and shuffle may not work. This is the vital piece of this exercise.

It is always prudent to perform a spot check of any complex Excel formula; to ensure the precedent and dependent cells are functioning correctly.

One Alternative

There is one alternative to the shuffling of columns of data. It involves changing the LARGE with the SMALL function inside the HLOOKUP formula in the 4th step. As in the following formula in cell C7:

=HLOOKUP(LARGE($C$9:$L$9,C8),$C$9:$L$10,2,FALSE)
becomes
=HLOOKUP(SMALL($C$9:$L$9,C8),$C$9:$L$10,2,FALSE)

Conclusion

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. 

By applying the above 4 steps, we are able to shuffle columns of data; without the use of esoteric VBA macros.