Invert non-consecutive data column in Excel

Let's say, that you have a one or more columns, in which you would like to invert data. What is most important -- data does not follow step-by-step (in which case you'd use simple data sorting function in Excel), It can have something like –1, 5, 76, 23, –65, 124 in each following row of column and you want it to have like 124, –65, 23, 76, 5, –1. How to do this, if you deal with hundreds or thousands of rows, so manual change is not an option?

I found many approaches to solve this quite simple problem. Including some strange VBA script. I haven't tested it, because it freaks me up (the general idea of hiring scripting language to so simple task).

However, the same Polish forum gave me just a brilliant solution.

It says (for those of you, who're not familiar with Polish) that all you have to do is:

  • add new column next to column or columns, you want to sort,
  • fill it with consecutive, rising numbers up to the length of "real" columns,
  • select all columns you want to invert, including newly added one,
  • do simple data sorting, numeric, rising, basing on added column,
  • delete added extra column.

Voila! Brilliant and perfect solution. Nothing to add!

Leave a Reply