Sort list by size

[Mrmath9]

Hi, i want to analyse a big amount of number and would love some help.

Column A = time
Column B = Price
Column C = Size

Aprox 200 000 rows :O

How can i sort this so the 20 biggest by size show up in Column D (biggest on top) followed by it´s time in Column E and it´s price Column F.

If i see big size at row 750, it would be great if i also could get the price for the previous (row 749) in Column G and the price for (row 751) in column H.

I want to see the 20 biggest by size like this.

Would this be possible? Hope you understand how i mean.
Can someone help me please, how can i code this?
I would appriciate it a lot. Even if you could just help with first section it would be great.
Best regards, thank you.
Dave.

 

[Sufiyan97]

That was it, now it works on one of my computors :D Good. I changed my regional settings to US. Is there any difference between formulas on older version excel or same should work? One thing i wonder though. Everything works know but clock is 0.625 instead of 15:00 :D Why?

Regarding formulas above formulas should work in Excel 2013

 

[Mrmath9]

Just change format from General to time

View attachment 54626

Perfect, thank you

 

[Mrmath9]

You can get the arrangement you want just by dragging the formulas around:

Book1
	A	B	C	D	E	F	G	H	I
1	Time	Price	Size	Top 20 Sizes	Time	Prev Price	Price	Next Price	Row
2	1:00	1	192	498	15:00	14	15	16	16
3	2:00	2	151	493	4:00	3	4	5	5
4	3:00	3	268	493	20:00	19	20	0	21
5	4:00	4	493	417	19:00	18	19	20	20
6	5:00	5	128	383	17:00	16	17	18	18
7	6:00	6	103	380	9:00	8	9	10	10
8	7:00	7	253	371	11:00	10	11	12	12
9	8:00	8	272	295	14:00	13	14	15	15
10	9:00	9	380	290	16:00	15	16	17	17
11	10:00	10	127	272	8:00	7	8	9	9
12	11:00	11	371	268	3:00	2	3	4	4
13	12:00	12	229	262	18:00	17	18	19	19
14	13:00	13	201	253	7:00	6	7	8	8
15	14:00	14	295	229	12:00	11	12	13	13
16	15:00	15	498	201	13:00	12	13	14	14
17	16:00	16	290	192	1:00		1	2	2
18	17:00	17	383	151	2:00	1	2	3	3
19	18:00	18	262	128	5:00	4	5	6	6
20	19:00	19	417	127	10:00	9	10	11	11
21	20:00	20	493	103	6:00	5	6	7	7
Sheet40

Cell Formulas
Range	Formula
D2:D21	D2	=LARGE($C$2:$C$100,ROWS($C$2:$C2))
E2:E21	E2	=INDEX(A:A,$I2)
F2:F21	F2	=IF(I2>2,INDEX($B:$B,I2-1),"")
G2:G21	G2	=INDEX(B:B,$I2)
H2:H21	H2	=INDEX($B:$B,I2+1)
I2:I21	I2	=AGGREGATE(15,6,ROW($A$2:$A$100)/($C$2:$C$100=$D2),COUNTIF($D$2:$D2,$D2))

As far as changing the formulas, in the D and I formulas, you have to change the range to have the maximum number of rows you want. 200,000 based on your original question. I'd recommend against making it the whole length of the sheet for performance reasons. Change it to something like 300,000. You don't need to change the formulas if you want the top 30 instead of the top 20, just drag them further down the columns.

You can get the arrangement you want just by dragging the formulas around:

Book1
	A	B	C	D	E	F	G	H	I
1	Time	Price	Size	Top 20 Sizes	Time	Prev Price	Price	Next Price	Row
2	1:00	1	192	498	15:00	14	15	16	16
3	2:00	2	151	493	4:00	3	4	5	5
4	3:00	3	268	493	20:00	19	20	0	21
5	4:00	4	493	417	19:00	18	19	20	20
6	5:00	5	128	383	17:00	16	17	18	18
7	6:00	6	103	380	9:00	8	9	10	10
8	7:00	7	253	371	11:00	10	11	12	12
9	8:00	8	272	295	14:00	13	14	15	15
10	9:00	9	380	290	16:00	15	16	17	17
11	10:00	10	127	272	8:00	7	8	9	9
12	11:00	11	371	268	3:00	2	3	4	4
13	12:00	12	229	262	18:00	17	18	19	19
14	13:00	13	201	253	7:00	6	7	8	8
15	14:00	14	295	229	12:00	11	12	13	13
16	15:00	15	498	201	13:00	12	13	14	14
17	16:00	16	290	192	1:00		1	2	2
18	17:00	17	383	151	2:00	1	2	3	3
19	18:00	18	262	128	5:00	4	5	6	6
20	19:00	19	417	127	10:00	9	10	11	11
21	20:00	20	493	103	6:00	5	6	7	7
Sheet40

Cell Formulas
Range	Formula
D2:D21	D2	=LARGE($C$2:$C$100,ROWS($C$2:$C2))
E2:E21	E2	=INDEX(A:A,$I2)
F2:F21	F2	=IF(I2>2,INDEX($B:$B,I2-1),"")
G2:G21	G2	=INDEX(B:B,$I2)
H2:H21	H2	=INDEX($B:$B,I2+1)
I2:I21	I2	=AGGREGATE(15,6,ROW($A$2:$A$100)/($C$2:$C$100=$D2),COUNTIF($D$2:$D2,$D2))

As far as changing the formulas, in the D and I formulas, you have to change the range to have the maximum number of rows you want. 200,000 based on your original question. I'd recommend against making it the whole length of the sheet for performance reasons. Change it to something like 300,000. You don't need to change the formulas if you want the top 30 instead of the top 20, just drag them further down the columns.

Hi Eric, it works great. I wonder. On my other computor i only have open office. Is there a way changing the code so it work in open office aswell? If so how?? I´m not sure if you guys are into open office aswell or just excel? :D Thank you.

 

[Eric W]

Well, I don't have Open Office, so I can't help with that. Undoubtedly there are some things that would transfer, but I don't know what. There are probably some people here that know Open Office who might help. I'd suggest you open a new thread in the "General Discussion and Other Applications" forum, and ask your question there. You might get a bite. Good luck!

 

[Mrmath9]

Well, I don't have Open Office, so I can't help with that. Undoubtedly there are some things that would transfer, but I don't know what. There are probably some people here that know Open Office who might help. I'd suggest you open a new thread in the "General Discussion and Other Applications" forum, and ask your question there. You might get a bite. Good luck!

It´s okey. Yes i will try opening new thread and see if someone know. Thanks anyway.

Convert from Excel to Open office

D2 =LARGE($C$2:$C$100,ROWS($C$2:$C2)) E2 =INDEX(A:A,$I2) F2 =IF(I2>2,INDEX($B:$B,I2-1),"") G2 =INDEX(B:B,$I2) H2 =INDEX($B:$B,I2+1) I2 =AGGREGATE(15,6,ROW($A$2:$A$100)/($C$2:$C$100=$D2),COUNTIF($D$2:$D2,$D2)) Hi, i have this code in excel. Can someone please help me convert it so it works in...

www.mrexcel.com