Top 10% of Sorted Data

[andy3919]

Good Day All.

I have a data set of about 600 records. I want to be able to extract the Top 10% of the records and get rid of the other 90% - the values can change over time, so I always want to start with the full 600 records.

I am aware of the conditional formatting function that will identify the Top 10%, but I was looking for VBA language (does not have to be VBA - just automated), that calculated the 10% subset and either moved it to a different sheet, OR deleted the bottom 90% - in either instance the result being just the list of the top 10% of the data set.

I hope that makes sense.

Thanks in advance!!!

 

[GaryOfah]

If you don't need them, you don't have to pull formula across the whole range under “name by rank”.
Maybe 50% is enough? So that's 500 formulas less.
So that way you use less sheet load.
And you can delete unneeded if you don’t need same volume data.
So you can live with much less sheet load. E.g. delete some if it is of no use.
Maybe deduct some COUNTIF-s starting from 5,4,3...
I believe it can be probably even easier that this but for a start it is quite ok.

You can add macro and copy just values for archive.

 

[andy3919]

wow. Gary. Thank you!

I am plodding through it now. I really appreciate ALL of your effort!!!

 

[GaryOfah]

Thanks...


If you're having any more special wishes and ideas to implement - you can contact me also (for sure) via p.m.
As I said it can be done probably with easier formulas etc., but this method was I think also ok.

 

[andy3919]

Gary...this works GREAT! Thanks again.

 

[GaryOfah]

He he I'm glad it works!