Here's a simpler way to achieve the auto-sort in adjacent cols to the right,
using non-array formulas, and with tie-breakers built-in
Source data assumed in cols A and B, from row1 down
Key col = col B, which contains the numbers
In C1: =IF(B1="","",B1-ROW()/10^10)
In D1: =IF(E1="","",ROW())
In E1:
=IF(ROW()>COUNT($C:$C),"",INDEX(A:A,MATCH(LARGE($C:$C,ROW()),$C:$C,0)))
Copy E1 to F1. Select C1:F1, copy down to cover the max expected extent of
source data in cols A and B, say down to F100? Minimize/hide away col C.
Cols D to F will return the required results, in descending auto-sorted
order. Col D provides a serial auto-numbering for the result lines. Tied
cases, if any, will be returned in the same relative order that they appear
within the source.
And if the source data starts in other than row1 down, eg it starts in row2
down,
just replace ROW() in all 3 formulas with ROWS($1:1) [ROW() is sensitive to
the cell it's in]
viz., use this set instead:
In C2: =IF(B2="","",B2-ROWS($1:1)/10^10)
In D2: =IF(E2="","",ROWS($1:1))
In E2:
=IF(ROWS($1:1)>COUNT($C:$C),"",INDEX(A:A,MATCH(LARGE($C:$C,ROWS($1:1)),$C:$C,0)))
Rest of construct is the same. Leave C1:F1 empty.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400, Files:361, Subscribers:58
xdemechanik
---