Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Sorting question | New Users to Excel | |||
Non updatable Unique Random Number | Excel Worksheet Functions | |||
Changing a Number in a Column Using Arrays | Excel Worksheet Functions | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
Sorting Numbers with Multiple Decimals (cont.) | Excel Discussion (Misc queries) |