Top 3 Accounts's

[Chris84]

Hi

I need to extract the top three values from in a column and copy the account and value to another sheet.

The table range is not fixed, the account will always be in column A but the value could be in different columns dependent on which day.

The user needs to select a column number then the code will find the top three values and in say column G and copy the value and the account (Column A) to another sheet

I have tried using the large function which gives me values but how would you get the account and value?

Any help greatly appreciated

 

[Andrew Poulsom]

Here's a video that may help you:

Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates - YouTube

 

[Chris84]

Hi

And thanks for the response

Is there a way this could be edited so you can input into a box column G or H for example?

Thanks

 

[Andrew Poulsom]

What formula do you want to change? It would be easy with column numbers using INDEX.

 

[Chris84]

Hi

I have copied the template from the video which is amazing but have moved the manager to column A and the visits to column B.

Column B is the one that needs changing in the formulas so for example the user would put column G or 7 in cell B4

The formulas im using are

Number D8 Down
=IF(ROWS(D$8:D8)>$B$3,"",ROWS(D$8:D8))
Visits E8 Down
=IF(D8="","",LARGE($B$8:$B$17,D8))
Manager F8 Down
=IF(D8="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E8,ROW($B$8:$B$17)-ROW($B$8)+1),COUNTIF($E$8:E8,E8))))

Also is there a way of changing $B$8:$B$17 so it starts at 8 but runs till the bottom or the range

Thanks again

 

[Chris84]

Hi

I have copied the template from the video which is amazing but have moved the manager to column A and the visits to column B.

Column B is the one that needs changing in the formulas so for example the user would put column G or 7 in cell B4

The formulas im using are

Number D8 Down
=IF(ROWS(D$8:D8)>$B$3,"",ROWS(D$8:D8))
Visits E8 Down
=IF(D8="","",LARGE($B$8:$B$17,D8))
Manager F8 Down
=IF(D8="","",INDEX($A$8:$A$17,SMALL(IF($B$8:$B$17=E8,ROW($B$8:$B$17)-ROW($B$8)+1),COUNTIF($E$8:E8,E8))))

Also is there a way of changing $B$8:$B$17 so it starts at 8 but runs till the bottom or the range

Thanks again

 

[Andrew Poulsom]

You can use a dynamic name:

Excel Names -- Excel Named Ranges