Request for VBA code to select top 3 or 5 ,userwise audit samples

[msn18]

hi ,

I am new to VBA, i am looking for VBA code, to one of my workings,

i have a data in sheet1 wtih multiple rows and coloums, form which i need pick audit samples Top3 or Top5 based on Analyst names and Transaction Amount.

A1 [Analyst Name] B2 [Transaction Amount]

Mr X 100$
Mr Y 500$
Mr Z 200$
Mr A 1000$
Mr X 500$
Mr Y 1000$
Mr Z 100%

my data in sheet1 and output of samples to be in sheet2 ( attached sample excel work sheet with for referance)

please help with query ..

Thank you in advance

[cubangt]

You may want to look into the RANK formula along with the SUMPRODUCT formula

You can place this in your D5 cell:

Please Login or Register  to view this content.

Apply down and that will rank the list, you can then pull out the top x

[cubangt]

Forget the above, here is your file with a mix of formulas to give you the results.

I updated with the dollars so you can see why those were chosen as the top ones.

[msn18]

.........................

[msn18]

hi Sir,

I have tryed with above forumal, but it showing only the top3 samples of only 1Analyst, i need to select samples of each analyst with top3 of high value.

i have multiples rows and coulums in my workseet, so could you please help with VBA query if i input my data in sheet1 and my output should be with top3 / top5 of each Analyst .

thank you in advance

[nankw83]

Hi msn18,

Try below code based on your sample file ...

Please Login or Register  to view this content.

[cubangt]

hi Sir,

I have tryed with above forumal, but it showing only the top3 samples of only 1Analyst, i need to select samples of each analyst with top3 of high value.

i have multiples rows and coulums in my workseet, so could you please help with VBA query if i input my data in sheet1 and my output should be with top3 / top5 of each Analyst .

thank you in advance

You are correct, it shows the same analyst, because its that one that has the 3 highest dollar amounts. If you look at the top5 column it pulls in 2 others as well.. The formula i used is basing its ranking on the dollar amount.
If you are wanting to have it select the top 3 based on the pair of information, it will need some adjustments.

Take a look at nankw83 code and see if that helps..

[nankw83]

Thank you msn18 for the added Rep. points. If the provided solution takes care of your question, please take a moment & mark the thread as [SOLVED] from the Thread Tools above your first post