Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 26
Default Sorting by Row Number?

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!



  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,180
Default Sorting by Row Number?

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting question JoAnn Paules [MVP] New Users to Excel 5 September 30th 06 04:55 PM
Non updatable Unique Random Number Ian Excel Worksheet Functions 30 September 28th 06 08:19 PM
Changing a Number in a Column Using Arrays [email protected] Excel Worksheet Functions 3 September 12th 06 02:48 PM
vlookup with more than number to be retrieved martelie Excel Worksheet Functions 1 October 8th 05 07:33 AM
Sorting Numbers with Multiple Decimals (cont.) Intern Ian Excel Discussion (Misc queries) 5 September 21st 05 12:04 AM


All times are GMT +1. The time now is 06:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"