Sorting by Row Number?

R

Rothman

Can you sort a row by row numbers in the formulas of the values of the cells?

For instance, I have a 120x5 table which looks like this:


=K301 =L302 =M303 =N304 =O305
=K301 =L302 =M303 =N305 =O304
=K301 =L302 =M304 =N303 =O305
=K301 =L302 =M304 =N305 =O303
=K301 =L302 =M305 =N303 =O304

....and so forth

I'd like to sort the rows in my table by the row numbers indicated, like so:

=K301 =L302 =M303 =N304 =O305
=K301 =L302 =M303 =O304 =N305
=K301 =L302 =N303 =M304 =O305
=K301 =L302 =O303 =M304 =O305
=K301 =L302 =N303 =O304 =M305

...and so forth.

Can this be done?

Thanks again!
 
H

Herbert Seidenberg

Assume just one row at A1 and locations with one letter and 3 digits.
Erase the equal sign in the formulas to get this:
J926 F832 E127 I200 D185 H572 J532 D692 A687 D362
Name these 10 cells data.
In another 10 cell location, enter this array formula:
="^="&CHAR(RIGHT(LARGE(VALUE(RIGHT(data,3)*100+
CODE(LEFT(data,1))),COLUMN()),2))&
LEFT(LARGE(VALUE(RIGHT(data,3)*100+
CODE(LEFT(data,1))),COLUMN()),3)
You should get this sorted row:
^=J926 ^=F832 ^=D692 ^=A687 ^=H572 ^=J532 ^=D362 ^=I200 ^=D185 ^=E127
Copy > Paste Special > Values
and erase the caret character.
If your address format is variable, I recommend using R1C1 Ref Style.
Change the formula accordingly.
 
Top