Data to be ranked Groupwise and then TOP 5 of Each Group to be pasted in other sheet.

Dear Gurus,

I seek your help through VBA.

I have a data in which I need to do a GROUPWISE RANKING and then to find Top 5 of Each group, which is to be pasted in next sheet.

I attached a sample workbook for your kind reference.
Any help is highly appreciated.

Thanks in advance. I hope some one will give solution.

acsishere.

Hi

This will go part way to your solution. It uses the inbuilt RANK function to determine the ranking, so it doesn't come up with the same output due to how it handles duplicates.

Please Login or Register  to view this content.

rylo

That's amazing! This is the best solution that what I expected.

Is there any possibility to rank (descending/ascending) as following:

index / rank / excel's ranking
450 / 1 / 1
400 / 2 / 4
350 / 3 / 5
450 / 1 / 1
350 / 3 / 5
450 / 1 / 1

In this example, index is ranked accordingly, without skipping any ranking number. Whereas the excel skips the rank number if it find the rank more than one.

How to get rid of this problem?

Any help please.

acsishere.

Again, Thanks for your help in writing the below code. It is very short and sweet and nicely working. In this regard, I have a couple of queries:

The code is:

Please Login or Register  to view this content.

When I run the above code, I am unable to do the following:
1) When I added a new group (say 5 or 6 or 7... and so on..) it is not added in the sheet2. Is it necessary to change any code when some new groups are added in "Data to be Ranked" sheet?

2) When it is not showing ranking in the way which I required, alternatively what code should be included with the above to sort groupwise and then Indexwise in sheet3? So that, I can manually select the data as I required.
Please help.

Thanks in advance.

acsishere.

Hi

1)
change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) Adding sorting code to the raw data isn't a problem. How would you expect to pause the code to nominate which items to copy across? (a) have 2 code items - 1 to sort, 1 to transfer nominated items? If you would be happy with the alternative rank code option I gave you, then the code could be adapted to include that formula.

rylo

Thanks for your suggestion & the code.

Sir, when I copied the suggested code,

1) it gives the following error message.
"object variable or With block variable not set". And, it is not selecting/copying the particulars (in sheet2) beyond group 4. How to solve it?

2) It is better to have the ranking order as given in the first code (default ranking formula). If it sorts Top 10 of each group, then I can manually select till which rank I needed.

Seeking your expertise. Thanks in advance.

acsishere.

Good Morning & Good Day!

I received the file which was giving another solution only (there was no code within that) for ranking.

Whereas, Sir, I mentioned two problems in the previous post.

1) The code is showing error message,

Please Login or Register  to view this content.

when the line

Please Login or Register  to view this content.

is changed to :

Please Login or Register  to view this content.

2) It is copying only a maximum of 4 groups in sorted order. If it sorts all groups and shows Top 10 among them, then I can manually choose till which rank I want.

Seeking your expertise.

Thanks in advance.

acsishere.

Good day & Best wishes!

Thanks for your expertise. It still gives the error message. When the No. of records available are less than 5 then it shows error.

Please give me the solution.

Thanks in advance.

acsishere.

Hi

Change the line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

see how that goes.

rylo

Thank you very much for your kind support.

The modified code is nicely working. Whereas when I tried to incorporate into my workbook, I am facing some errors. I don't know where I am committing mistakes.

Hence, once again, I seek your kind help to accomodate them properly. I attached the file (in which I am originally working) for your kind ref.

I sincerely thanking you for your kindness.

Thanks a lot,

acsishere.

I made a small change in the attached file (in which incorporation required).

Thanks in advance.

acsishere.

Best Wishes!

I adopted the below code into my workbook very nicely. I am having some 4500 data into that. The code that I am using is:

Please Login or Register  to view this content.

I am facing two problems when executed the above macro. I explained the same in the attached file.

Please help me to solve this trouble.

Thanks in advance.

acsishere.

Any Solution for the above?

Thanks in advance.

acsishere.

Hi

Couple of mods to the code for you. See how it goes.

Please Login or Register  to view this content.

rylo

My sincere & heartiest thanks to you. It is exactly modified as I required.

Thanks a lot......

acsishere.