# Top 3 Accounts's

#### Chris84

##### Board Regular
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

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

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

Code:
``````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

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

Code:
``````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

Replies
4
Views
202
Replies
1
Views
182
Replies
3
Views
264
Replies
5
Views
211
Replies
0
Views
260

1,207,199
Messages
6,077,023
Members
446,251
Latest member
dpf220

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back