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

why not just use (record and cleanup)
datafilterautofiltertop 10etc

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nick" wrote in message
...
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

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.



--
Regards,
Tom Ogilvy


"Nick" wrote:

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

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:D$1000,2,FALS E)
Drag down to E10 for top ten risks.



Mike


"Nick" wrote:

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

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

--
Regards,
Tom Ogilvy

"Mike H" wrote:

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:D$1000,2,FALS E)
Drag down to E10 for top ten risks.



Mike


"Nick" wrote:

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

agreed thanks for that

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Mike H" wrote:

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:D$1000,2,FALS E)
Drag down to E10 for top ten risks.



Mike


"Nick" wrote:

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

Many thanks Tom I will try this asap

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Mike H" wrote:

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:D$1000,2,FALS E)
Drag down to E10 for top ten risks.



Mike


"Nick" wrote:

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