PDA

View Full Version : [SOLVED] How to sort a list of numbers and eject one special number of this list?



Cinema
12-07-2015, 07:26 AM
Hello,

I have a column in Excel filled with 50.000 numbers. The first number is in G6 the last in G50005.
Now I want to sort these numbers (increasing) and select the 250th number and enter it in Range N5.
In the end I want just to see the 250th number of the sorted numbers. There should not be a sorted column of the numbers or something else. There should be only my origin Column with the numbers and the number in Range N5.

p45cal
12-07-2015, 07:42 AM
In N5, the formula:
=LARGE($G$6:$G$50005,250)
?

Cinema
12-07-2015, 07:58 AM
Wow I don't know this formula. Thank you. If I want to involve it in my vba Code then my I use the same formula?

p45cal
12-07-2015, 08:19 AM
in vba either
Application.WorksheetFunction.Large
or
Application.Large
eg.
zz = Application.WorksheetFunction.Large(Range("G6:G50005"), 250)
be aware that LARGE returns what would be in the 250th row of a sorted list, so if there are say 300 instances of whatever the maximum number is, the 250th will be that max value, not the 250th member of a list of unique numbers:

The 10th largest number in this list:
35,35,33,33,33,33,33,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8 ,7,6,5,4,3,2,1
could be 24 or 19 depending on how you want to treat duplicates.

Cinema
12-07-2015, 08:23 AM
Thank you :)

snb
12-07-2015, 10:00 AM
Now I want to sort these numbers (increasing) and select the 250th number and enter it in Range N5

Isn't it ?


sub M_snb()
[n5]=[small(G6:G50005,250)]
end sub

p45cal
12-07-2015, 11:06 AM
Isn't it ?Yes!