Using a Formula to Sort Data in a Seperate Location to Original Data
I have data in a table, and I want to repeat the table below, but with one of the columns sorted, and all the other entries on each side of it automatically sorted with it. In the attached example I would like to sort Column C in descending order, but the other columns need to sort with it. I need to use a formula, I can't use the sort function because I want the data sorted in a seperate location to the source data.
Thanks
| Attachment | Size |
|---|---|
| Sorting.xlsx | 9.75 KB |
I presume that copying the
I presume that copying the table and then sorting that is precluded for some reason so as a formula based solution and located to side as in the example how about
=MATCH(LARGE(IFERROR($C$3:$C$15 + ROW($C$3:$C$15) / 1000000,-ROW($C$3:$C$15) / 1000000),ROW($C$3:$C$15)-2),IFERROR($C$1:$C$15 + ROW($C$1:$C$15) / 1000000,-ROW($C$1:$C$15) / 1000000),0)
array entered to give the required sequence and then use this in a series of INDIRECTs to pick up the required data.
I put the sequence calculation in a helper column for ease and clarity but it could be embedded in the INDIRECTs if required.
INDIRECTs volatility could be a problem too if the table is large enough.
The ranges starting at C1 are a cheat so that the sequence returns the row rather than the position but could ( and probably should ) be replaced with a row offset calculation.