Sort list by size

Mrmath9

New Member
Joined
Jan 5, 2022
Messages
15
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.
 
Upvote 0
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. :)
 
Upvote 0
Hi Mrmath9
Welcome to the Board!

Is that what you want

Book3
ABCDEFG
1TimePriceSizeSizeTimePrice
210:53 PM175901:53 PM16
311:53 PM2768912:53 PM15
412:53 AM3778811:53 AM14
51:53 AM4788710:53 AM13
62:53 AM579869:53 AM12
73:53 AM680858:53 AM11
84:53 AM781847:53 AM10
95:53 AM882836:53 AM9
106:53 AM983825:53 AM8
117:53 AM1084814:53 AM7
128:53 AM1185803:53 AM6
139:53 AM1286792:53 AM5
1410:53 AM1387781:53 AM4
1511:53 AM14887712:53 AM3
1612:53 PM15897611:53 PM2
171:53 PM16907510:53 PM1
182:53 PM1710505:53 PM20
193:53 PM1820304:53 PM19
204:53 PM1930203:53 PM18
215:53 PM2050102:53 PM17
22
Sheet1
Cell Formulas
RangeFormula
D2:D21D2=LARGE($C$2:$C$200000,ROW()-1)
E2:E21E2=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:F21F2=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)))
 
Upvote 0
Also consider:

Book1
ABCDEFGHI
1TimePriceSizeTop 20 SizesTimePriceRowPrev PriceNext Price
21:00119249815:0015161416
32:0021514934:004535
43:00326849320:002021190
54:00449341719:0019201820
65:00512838317:0017181618
76:0061033809:00910810
87:00725337111:0011121012
98:00827229514:0014151315
109:00938029016:0016171517
1110:00101272728:008979
1211:00113712683:003424
1312:001222926218:0018191719
1413:00132012537:007868
1514:001429522912:0012131113
1615:001549820113:0013141214
1716:00162901921:0012 2
1817:00173831512:002313
1918:00182621285:005646
2019:001941712710:001011911
2120:00204931036:006757
22
Sheet40
Cell Formulas
RangeFormula
D2:D21D2=LARGE($C$2:$C$100,ROWS($C$2:$C2))
E2:F21E2=INDEX(A:A,$G2)
G2:G21G2=AGGREGATE(15,6,ROW($A$2:$A$100)/($C$2:$C$100=$D2),COUNTIF($D$2:$D2,$D2))
H2:H21H2=IF(G2>2,INDEX($B:$B,G2-1),"")
I2:I21I2=INDEX($B:$B,G2+1)
 
Upvote 0
Also consider:

Book1
ABCDEFGHI
1TimePriceSizeTop 20 SizesTimePriceRowPrev PriceNext Price
21:00119249815:0015161416
32:0021514934:004535
43:00326849320:002021190
54:00449341719:0019201820
65:00512838317:0017181618
76:0061033809:00910810
87:00725337111:0011121012
98:00827229514:0014151315
109:00938029016:0016171517
1110:00101272728:008979
1211:00113712683:003424
1312:001222926218:0018191719
1413:00132012537:007868
1514:001429522912:0012131113
1615:001549820113:0013141214
1716:00162901921:0012 2
1817:00173831512:002313
1918:00182621285:005646
2019:001941712710:001011911
2120:00204931036:006757
22
Sheet40
Cell Formulas
RangeFormula
D2:D21D2=LARGE($C$2:$C$100,ROWS($C$2:$C2))
E2:F21E2=INDEX(A:A,$G2)
G2:G21G2=AGGREGATE(15,6,ROW($A$2:$A$100)/($C$2:$C$100=$D2),COUNTIF($D$2:$D2,$D2))
H2:H21H2=IF(G2>2,INDEX($B:$B,G2-1),"")
I2:I21I2=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.
 
Upvote 0
Hi Mrmath9
Welcome to the Board!

Is that what you want

Book3
ABCDEFG
1TimePriceSizeSizeTimePrice
210:53 PM175901:53 PM16
311:53 PM2768912:53 PM15
412:53 AM3778811:53 AM14
51:53 AM4788710:53 AM13
62:53 AM579869:53 AM12
73:53 AM680858:53 AM11
84:53 AM781847:53 AM10
95:53 AM882836:53 AM9
106:53 AM983825:53 AM8
117:53 AM1084814:53 AM7
128:53 AM1185803:53 AM6
139:53 AM1286792:53 AM5
1410:53 AM1387781:53 AM4
1511:53 AM14887712:53 AM3
1612:53 PM15897611:53 PM2
171:53 PM16907510:53 PM1
182:53 PM1710505:53 PM20
193:53 PM1820304:53 PM19
204:53 PM1930203:53 PM18
215:53 PM2050102:53 PM17
22
Sheet1
Cell Formulas
RangeFormula
D2:D21D2=LARGE($C$2:$C$200000,ROW()-1)
E2:E21E2=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:F21F2=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 :)
 
Upvote 0
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
ABCDEFGHI
1TimePriceSizeTop 20 SizesTimePrev PricePriceNext PriceRow
21:00119249815:0014151616
32:0021514934:003455
43:00326849320:001920021
54:00449341719:0018192020
65:00512838317:0016171818
76:0061033809:00891010
87:00725337111:0010111212
98:00827229514:0013141515
109:00938029016:0015161717
1110:00101272728:007899
1211:00113712683:002344
1312:001222926218:0017181919
1413:00132012537:006788
1514:001429522912:0011121313
1615:001549820113:0012131414
1716:00162901921:00 122
1817:00173831512:001233
1918:00182621285:004566
2019:001941712710:009101111
2120:00204931036:005677
Sheet40
Cell Formulas
RangeFormula
D2:D21D2=LARGE($C$2:$C$100,ROWS($C$2:$C2))
E2:E21E2=INDEX(A:A,$I2)
F2:F21F2=IF(I2>2,INDEX($B:$B,I2-1),"")
G2:G21G2=INDEX(B:B,$I2)
H2:H21H2=INDEX($B:$B,I2+1)
I2:I21I2=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.
 
Upvote 0
You can get the arrangement you want just by dragging the formulas around:

Book1
ABCDEFGHI
1TimePriceSizeTop 20 SizesTimePrev PricePriceNext PriceRow
21:00119249815:0014151616
32:0021514934:003455
43:00326849320:001920021
54:00449341719:0018192020
65:00512838317:0016171818
76:0061033809:00891010
87:00725337111:0010111212
98:00827229514:0013141515
109:00938029016:0015161717
1110:00101272728:007899
1211:00113712683:002344
1312:001222926218:0017181919
1413:00132012537:006788
1514:001429522912:0011121313
1615:001549820113:0012131414
1716:00162901921:00 122
1817:00173831512:001233
1918:00182621285:004566
2019:001941712710:009101111
2120:00204931036:005677
Sheet40
Cell Formulas
RangeFormula
D2:D21D2=LARGE($C$2:$C$100,ROWS($C$2:$C2))
E2:E21E2=INDEX(A:A,$I2)
F2:F21F2=IF(I2>2,INDEX($B:$B,I2-1),"")
G2:G21G2=INDEX(B:B,$I2)
H2:H21H2=INDEX($B:$B,I2+1)
I2:I21I2=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 :)
 
Upvote 0

Forum statistics

Threads
1,224,743
Messages
6,180,687
Members
452,994
Latest member
Janick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top