Excel - Sorting using formula

I have the following rows
A         B  C    D          E
Bill A   5   1     Bill B    8
Bill B   8   2     Bill A    5
Bill C   2   3     Bill D    3
Bill D   3   4     Bill C    2

I column D I have this formula

=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 E



=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 as



Bill B

Bill B

Bill A

Bill D



I hav we tried changing the formula to



=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 B

Bill A

Bill D




What I'm actually looking for is a formula that produces



Bill B    8

Bill C    8

Bill A    5

Bill D   3



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 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
---

It works perfect. Thank you
Jan

It works perfectly but I dont understand tthe /10^10 part. Could you please
explain that?

Jan

Sorry. I think I got the idea myself. Its to separate to equal numbers by
subtracting a small fraction from each of them.

Jan

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
---

Yes, essentially..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400, Files:361, Subscribers:58
xdemechanik
---

Hello Jan,

A general approach which also works for text (not only numbers):
http://www.sulprobil.com/html/sorting.html

Regards,
Bernd

Thank you. Ineresting article.

Jan

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

Hi Sir,



I would like to have access to this forum . I'm a beginner in Excel please allow me.

Hi Sir,





I have gone through your sorting technique.



But its giving me some error



I have fields as listed below:



Column A     Column B    Column c   Total



Peter           123       12         135



Sam            1234         1        1235



Kelly           121       34          155





I want to sort the fields like listed below





Column A      Column B    Column C     Total



Sam            1234         1        1235



Kelly           121       34          155



Peter           123       12         135





please help me with the sorting formula



Will be waiting for your message.



Andy,

Hi Sir,





I have gone through your sorting technique.



But its giving me some error



I have fields as listed below:



Column A     Column B    Column c   Total



Peter           123       12         135



Sam            1234         1        1235



Kelly           121       34          155





I want to sort the fields like listed below





Column A      Column B    Column C     Total



Sam            1234         1        1235



Kelly           121       34          155



Peter           123       12         135





please help me with the sorting formula



Will be waiting for your message.



Andy,

I'm trying to do something fairly similar, but I cannot get the data to populate throughout the entire range...



Data starts in column B, row 5 and stops in column c, row 18.

B       C        F      G        H       I

abc	60	60	1	mno	50

def	55	55	2	pqr	53

ghi	63	63	3	bcd	54

jkl	57	57	4	def	55

mno	50	50	5	nop	56

pqr	53	53	6	stu	56

stu	56	56	7	jkl	57

vwx	60	60	8	hij	59

yza	60	60	9	0	0

bcd	54	54	10	yza	60

efg	61	61	11	vwx	60

hij	59	59	12	abc	60

klm	67	67	13	efg	61

nop	56	56	14	ghi	63



You can see the error in index row 9, actual row 13...



Any help would be MUCH appreciated!!!