I used the formula suggested by Max (Singapore) on October 3rd 2008.
It works a treat!
HOWEVER
The ROW formula is not recognised in Xcelsius.
I want dynamic Pareto charts for the daily and weekly product performance reports - but these have to be in Xcelsius
Any ideas what other formula i can use?
It has to cope with multiple (same) values
In hope
PBM
Max wrote:
WelcomeThat's the tiebreaker part.
03-Oct-08
Welcom
That's the tiebreaker part. In col C, the term: B1-ROW()/10^1
when copied down will produce a unique series of arb numbers which is
v.closely associated (in descending order) with the actual numbers in col B,
even if col B were to contain the same number(s) (ie ties) scattered here
and there in the col
These arb numbers in col C are then exact-matched within itself by the term
MATCH(LARGE($C:$C,ROW()),$C:$C,0), o
MATCH(LARGE($C:$C,ROWS($1:1)),$C:$C,0
to ultimately produce the required descending auto-sort in cols E &
--
Ma
Singapor
http://savefile.com/projects/23689
Downloads:18,400, Files:361, Subscribers:5
xdemechani
---
Previous Posts In This Thread:
Sorting using formula
I have the following row
A B C D
Bill A 5 1 Bill B
Bill B 8 2 Bill A
Bill C 2 3 Bill D
Bill D 3 4 Bill C
I column D I have this formul
=INDEX($A$1:$A$4,ROW(INDIRECT(ADDRESS(MATCH(LARGE($B$1:$B$4,C1),$B$1:$B$4,0),1))),1
That I use to produce the list and in column
=LARGE($B$1:$B$4;C1
Actually to sort the two first rows without sorting them. But the problem
is, that if two or more entries in column B is the same, it does not work.
If Bill B and C both has the valueof 8 the list in column D will appear a
Bill
Bill
Bill
Bill
I hav we tried changing the formula t
=INDEX($A$1:$A$4,ROW(INDIRECT(ADDRESS(MATCH(LARGE(IF(FREQUENCY($B$1:$B$4,$B$1:$B$4)>0,$B$1:$B$4),C1),$B$1:$B$4,0),1))),1
But tihs produces this list instead
Bill
Bill
Bill
What I'm actually looking for is a formula that produce
Bill B
Bill C
Bill A
Bill D
so that no matter how many people have the same value, they are all shown in
the right order
Can it be done
Jan
Here's a simpler way to achieve the auto-sort in adjacent cols to the
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-i
Source data assumed in cols A and B, from row1 dow
Key col = col B, which contains the number
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
--
Ma
Singapor
http://savefile.com/projects/23689
Downloads:18,400, Files:361, Subscribers:5
xdemechani
---
Re: Sorting using formula
It works perfect. Thank yo
Ja
Max wrote:
It works perfectly but I dont understand tthe /10^10 part.
It works perfectly but I dont understand tthe /10^10 part. Could you pleas
explain that
Ja
Jan Kronsell wrote:
Sorry. I think I got the idea myself.
Sorry. I think I got the idea myself. Its to separate to equal numbers by
subtracting a small fraction from each of them.
Jan
Jan Kronsell wrote:
WelcomeThat's the tiebreaker part.
Welcome
That's the tiebreaker part. In col C, the term: B1-ROW()/10^10
when copied down will produce a unique series of arb numbers which is
v.closely associated (in descending order) with the actual numbers in col B,
even if col B were to contain the same number(s) (ie ties) scattered here
and there in the col.
These arb numbers in col C are then exact-matched within itself by the term:
MATCH(LARGE($C:$C,ROW()),$C:$C,0), or
MATCH(LARGE($C:$C,ROWS($1:1)),$C:$C,0)
to ultimately produce the required descending auto-sort in cols E & F
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400, Files:361, Subscribers:58
xdemechanik
---
Re: Sorting using formula
Yes, essentially..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400, Files:361, Subscribers:58
xdemechanik
---
Re: Sorting using formula
Thank you. Ineresting article.
Jan
Bernd P wrote:
Hello Jan,A general approach which also works for text (not only
Hello Jan,
A general approach which also works for text (not only numbers):
http://www.sulprobil.com/html/sorting.html
Regards,
Bernd
Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Repeating Structures Table Looping and Table Extract
http://www.eggheadcafe.com/tutorial...0-a5704fe31a76/biztalk-repeating-structu.aspx