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]

Hello @Eric W
Just for curiosity why you have added columns for Previous Price, Next Price and Row?

 

[Eric W]

Hello @Eric W
Just for curiosity why you have added columns for Previous Price, Next Price and Row?

The original post (the 7th line) asked for the previous/next prices. Post 4 asked for the row. I pretty much included everything the OP asked for, even though it was scattered in multiple posts.

 

[Sufiyan97]

The original post (the 7th line) asked for the previous/next prices. Post 4 asked for the row. I pretty much included everything the OP asked for, even though it was scattered in multiple posts.

Thanks Erick, Now I got that.
I think I am a beginner in reading full requirement as well

 

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

Hi I have a problem, i can´t get it to work yet. When i use excel it´s like it can´t read the formulas. I´ve tried both on new excel and old (2010 excel). Why can this be? :O Would this work on excel 2010 you think? So yea, excel don´t read the formulas and not sure why?

 

[Sufiyan97]

What errors you are finding?

 

[Mrmath9]

What errors you are finding?

When i type the formulas a window come up it says:

There´s a problem with this formula. Not trying to type a formula? When the first character is an equal ("=")or minus("=")sign, Excel thinks it´s a formula: * You type =1+1, cell shows: 2
to get around this, type an apostrophe (') first: * you type '=1+1, cell shows: =1+1.

Not sure why this come up because it´s formula i want to write :D So nothing happen with the formula and this come up.
I don´t have text format i have general.

 

[Sufiyan97]

Can post the same formula you are using here?

 

[Mrmath9]

Oh, I read something online. Seem to be the regional setting i need to change, my keyboard layout and regional setting are probably different than English since i live in Europe in non speaking English country. That probably make my excel not beeing able to read the formulas correctly since there is different , ; . and all those symbols. Hm anyone know what i should change it to? :D

 

[Mrmath9]

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?

 

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

Just change format from General to time