How do you extract the top ten values in a table?

[Marcello do Guzman]

I have a table that includes five columns as follows:

Col 1 Col 2 Col 3 Col 4 Col 5

Lifestyle Cluster Radius 1 Radius 2 Radius 3 Combined

The table has 62 rows. Columns 2 through 5 are values (numbers). I
would like to be able extract from this table the top 9 values and
lump the sum of the other 53 values from column 5 and list them in
descending order without using the Data, Sort features in Excel. How
can I program this so that it performs this function and prodes a
listing?

Please respond via email: (e-mail address removed)

 

[Colo]

Hi Marcello,

I don't know why you don't want to use "Sort" method...
Please try "Application.WorksheetFunction.Large".

Here is a sample.

Dim Target As Range, i As Long
Set Target = Range("A2:A63")
For i = 1 To 9
Debug.Print "Top" & i & " is " & _
Application.WorksheetFunction.Large(Target, i)
Next
Set Target = Nothing


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters'

URL:http://www.interq.or.jp/sun/puremis/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

 

[Tom Ogilvy]

It is not clear what the result is.

Are the top values determined by the combined values in col 5 ?

would the end result be another sheet with 10 rows (top 9, plus sum of each
column for remainder)?

Regards,
Tom Ogilvy