Sorting Across cells

[Hatricke]

Is it possible to sort across cells? For example if i have data that is 10 rows by 10 columns can i sort it so highest value is in A1 next highest in A2 all the way to lowest value in J10?

Thanks

[Whizbang]

I don't know of a way to do it using the sort method, but here is a way to do it using formulas:

In some other range enter this formula into the first column (assuming the range starts in the first row, adjust to suit)
=SMALL($A$1:$J$10,ROW())

Copy down 10 cells.

In the next column, use

=SMALL($A$1:$J$10,ROW()+10)

Copy down.

Repeat for each column, adding ten.

Then just copy and paste as values in your original range and clear the temporary range.

If you use a temporary sheet for your formulas, you could enter this is A1

=SMALL('Sheet1'!$A$1:$J$10,ROW()+((Column()-1)*10))

Then copy down and accross

[Marcol]

Select the row to sort
Data > Sort > Options...
Orientation:= Left to right

[Whizbang]

Oops. I reread your post and realized you wanted the largest number in A1 and the smallest in J10. Replace the word "SMALL" in my formulas above with "LARGE".

[6StringJazzer]

Here is a mod to Whizbang's approach which allows you to type in one formula and then just copy it. It assumes that the data you want to sort starts in A1. Otherwise it needs a tweak to be more general.

See attached example.

(Marcol: Your solution is for sorting individual rows but the OP is looking to sort then entire range as a block.)

This solution is essentially what Whizbang provided in the later edit of that post.

[Marcol]

@ 6 String J
Aye, I noticed that later, thought I'd leave the post just in case.