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.

 

[Mrmath9]

Edit, just would need some help with this below. That would be awesome. How can i do this Anyone know how to code?

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.

 

[Mrmath9]

In Column G what row it happened on aswell

 

[Mrmath9]

Sorry i can´t delete previous. I changed my mind. Easiest would be to only see the 20 biggest by size (biggest on top) in column D and in E what row it happened.

 

[Sufiyan97]

Hi Mrmath9
Welcome to the Board!

Is that what you want

Book3
	A	B	C	D	E	F	G
1	Time	Price	Size	Size	Time	Price
2	10:53 PM	1	75	90	1:53 PM	16
3	11:53 PM	2	76	89	12:53 PM	15
4	12:53 AM	3	77	88	11:53 AM	14
5	1:53 AM	4	78	87	10:53 AM	13
6	2:53 AM	5	79	86	9:53 AM	12
7	3:53 AM	6	80	85	8:53 AM	11
8	4:53 AM	7	81	84	7:53 AM	10
9	5:53 AM	8	82	83	6:53 AM	9
10	6:53 AM	9	83	82	5:53 AM	8
11	7:53 AM	10	84	81	4:53 AM	7
12	8:53 AM	11	85	80	3:53 AM	6
13	9:53 AM	12	86	79	2:53 AM	5
14	10:53 AM	13	87	78	1:53 AM	4
15	11:53 AM	14	88	77	12:53 AM	3
16	12:53 PM	15	89	76	11:53 PM	2
17	1:53 PM	16	90	75	10:53 PM	1
18	2:53 PM	17	10	50	5:53 PM	20
19	3:53 PM	18	20	30	4:53 PM	19
20	4:53 PM	19	30	20	3:53 PM	18
21	5:53 PM	20	50	10	2:53 PM	17
22
Sheet1

Cell Formulas
Range	Formula
D2:D21	D2	=LARGE($C$2:$C$200000,ROW()-1)
E2:E21	E2	=INDEX($A$2:$A$200000,AGGREGATE(15,6,(ROW($A$2:$A$200000)-ROW($A$2)+1)/($C$2:$C$200000=D2),COUNTIF($D$2:D2,D2)))
F2:F21	F2	=INDEX($B$2:$B$200000,AGGREGATE(15,6,(ROW($A$2:$A$200000)-ROW($A$2)+1)/($C$2:$C$200000=D2),COUNTIF($D$2:D2,D2)))

 

[Eric W]

Also consider:

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

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

 

[Mrmath9]

Also consider:

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

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

This is perfect :O Diden´t know it was possible. I think ideal would be. A to E is perfect. F previous price, G price, H next price and I Row. If i want more rows do i need to change something in the formula? Can we make it unlimited from the beginning so i don´t need to worry :D I´m not as good on excel as you :D Fantastic. Thank you.

 

[Mrmath9]

Hi Mrmath9
Welcome to the Board!

Is that what you want

Book3
	A	B	C	D	E	F	G
1	Time	Price	Size	Size	Time	Price
2	10:53 PM	1	75	90	1:53 PM	16
3	11:53 PM	2	76	89	12:53 PM	15
4	12:53 AM	3	77	88	11:53 AM	14
5	1:53 AM	4	78	87	10:53 AM	13
6	2:53 AM	5	79	86	9:53 AM	12
7	3:53 AM	6	80	85	8:53 AM	11
8	4:53 AM	7	81	84	7:53 AM	10
9	5:53 AM	8	82	83	6:53 AM	9
10	6:53 AM	9	83	82	5:53 AM	8
11	7:53 AM	10	84	81	4:53 AM	7
12	8:53 AM	11	85	80	3:53 AM	6
13	9:53 AM	12	86	79	2:53 AM	5
14	10:53 AM	13	87	78	1:53 AM	4
15	11:53 AM	14	88	77	12:53 AM	3
16	12:53 PM	15	89	76	11:53 PM	2
17	1:53 PM	16	90	75	10:53 PM	1
18	2:53 PM	17	10	50	5:53 PM	20
19	3:53 PM	18	20	30	4:53 PM	19
20	4:53 PM	19	30	20	3:53 PM	18
21	5:53 PM	20	50	10	2:53 PM	17
22
Sheet1

Cell Formulas
Range	Formula
D2:D21	D2	=LARGE($C$2:$C$200000,ROW()-1)
E2:E21	E2	=INDEX($A$2:$A$200000,AGGREGATE(15,6,(ROW($A$2:$A$200000)-ROW($A$2)+1)/($C$2:$C$200000=D2),COUNTIF($D$2:D2,D2)))
F2:F21	F2	=INDEX($B$2:$B$200000,AGGREGATE(15,6,(ROW($A$2:$A$200000)-ROW($A$2)+1)/($C$2:$C$200000=D2),COUNTIF($D$2:D2,D2)))

Thank you

 

[Eric W]

This is perfect :O Diden´t know it was possible. I think ideal would be. A to E is perfect. F previous price, G price, H next price and I Row. If i want more rows do i need to change something in the formula? Can we make it unlimited from the beginning so i don´t need to worry :D I´m not as good on excel as you :D Fantastic. Thank you.

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.

 

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

Thank you so much, this is perfect. I will try it. I let you know if i get any problem. It looks amazing. Thank you it´s very apriciated