Keep dynamic data sorted

[nelsok]

The only thing i can think of to make my dynamic data always be sorted is to use a formula to sort it.

Does anyone have a formula for sorting data in descending order?

 

[fairwinds]

Hi,

Try:

C2: =SUMPRODUCT(--(A2<$A$2:$A$10))+COUNTIF($A$2:A2,A2)
D2: =INDEX($A$2:$A$10,MATCH(ROW()-ROW($D$1),$C$2:$C$10,0))

Both dragged down.

Book1
	A	B	C	D
1	List1		Rank	Sorted list
2	q		3	x
3	a		6	t
4	c		4	q
5	x		1	c
6	c		5	c
7	a		7	a
8	25		9	a
9	t		2	a
10	a		8	25
Sheet1

 

[nelsok]

this will work but the only issue I have is that there are other cells associated with the values being sorted... for example a1 has a name b1 has revenue and c1 has expenditure and d1 has b1/c1...


I would want to sort by d1 but still have the correct data associated with each cell.

is this possible?

 

[fairwinds]

Sure.

Column C is base line. Just use the same formula as in column D, copied to further columns, to refere to the other columns you would like to sort along.

 

[nelsok]

This is working perfectly. However is it possible to get the cells with "" to be at the end of the sort and not the begining?

 

[fairwinds]

Not sure what you mean. Cells with "", formula blanks will end up at the end. See:

Book1
	A	B	C	D
1
2	Q		5	Y
3	W		2	W
4			8	U
5	R		4	R
6			9	Q
7	Y		1	O
8	U		3	I
9	I		7
10	O		6
Sheet1

 

[nelsok]

The blanks in my sort seem to be showing at the top.

any idea why?

data for pro rating analysis3.xls
	G	H	I	J	K
459	321
460	322
461	323
462	324	1	2	2	UBSWarburg
463	325	1	3	3	BakerBotts
464	326	1	2	2	WeilGotshal
465	327	1	1	1	Halliburton/KBR
Sorted Data

 

[fairwinds]

I see.

="" is concidered "larger" than a number.


In my exhibit above, replace the formula in C2 with

=IF(A2<>"",COUNTIF($A$2:$A$10,">"&A2)+COUNTIF($A$2:A2,A2),COUNTA($A$2:$A$10)+1-COUNTIF($A$2:A2,A2))+IF(ISNUMBER(A2),COUNTA($A$2:$A$10)-COUNT($A$2:$A$10)-COUNTBLANK($A$2:$A$10))

 

[nelsok]

Thanks!



I took out the +1 at the end of a counta() and that seemed to have fixed the problem.

do you see a problem with doing it this way?

data for pro rating analysis3.xls
	G	H	I	J	K
149	147	0.3125	16	5	Latham&Watkins
150	129	0.25	4	1	KramerLevin
151	80	0.25	4	1	OberKaler
152	81	0.25	4	1	TroutmanSandersLLP
153	82	0.25	4	1	Jenner&Block
154	83	0.25	4	1	Shearman&Sterling
155	84	0.2	10	2	WellsFargo
156	585
157	584
158	583
159	582
160	581
Sorted Data

 

[fairwinds]

I don't see why you should need to take it away. If you have only numbers it should work but with a mix of text and numbers and formula blanks, I get an error without the 1.