please help me program excel to scan for top 10 highest values

[Guest]

Hello

Please help.

I'm trying to program Excel to run down a column of values, taking the top
10 highest values and populating another worksheet with the adjacent
reference for these values.

For example

If I have company registration numbers in one column and their corresponding
credit risk ratings in another column, I want excel to populate another
worksheet with the registration numbers of the top 10 most risky companies .

I can do this manually, but I want add a button so that my colleagues can
perform the same action with exactly the same result.

Any hints would be much appreciated and I would be willing to make a donation

Many thanks in advance, Nick

 

[Don Guillett]

why not just use (record and cleanup)
data>filter>autofilter>top 10>etc

 

[Guest]

Select you data then do
Data=>Filter=>Autofilter

in the column with the risk numbers, select the dropdown and select Top 10.

If you need a macro, then turn on the macro recorder (tools=>Macro=>Record a
new macro), then perform the action manually. (after the rows are hidden to
reveal only the top 10, then click in the data, do Ctrl+Shift+8 to select all
the data and do edit => copy , then go to the new sheet and do edit =>paste
==> that is if you still think you need to copy the data). No go back to the
data and do Data=>Filter to turn off the autofilter, then turn off the macro
recorder.

Now go to the VBE (Alt+F11) and look at the recorded code. That is pretty
much what you want - again if you still want a code approach.

 

[Guest]

Nick,

You can have a macro if you want one but this is less work and guaranteed to
give repeatable results.

Risk rating in column C
Company name in column D

This in E1
=VLOOKUP(LARGE(C$1:C$1000,ROW()),C$1$1000,2,FALSE)
Drag down to E10 for top ten risks.



Mike

 

[Guest]

The results will be repeatable, but may be repeatably incorrect if any of the
companies in the top 10 share the same risk rating.

 

[Guest]

agreed thanks for that

The results will be repeatable, but may be repeatably incorrect if any of the
companies in the top 10 share the same risk rating.

 

[Guest]

Many thanks Tom I will try this asap

The results will be repeatable, but may be repeatably incorrect if any of the
companies in the top 10 share the same risk rating.