-
Feb 1st, 2021, 02:46 PM
#1
Thread Starter
Addicted Member
[RESOLVED] Apply Ranking
Hi.
Wish to apply ranking method to the attached table.
1st Ranking I want to apply in Column K
Apply a ranking (1 being the oldest) to each individual on Overdue status (Column F) For example
Property Manager Gareth will have three tasks in Column D. 380 days, 95days 870 days. The day count column is in Column H. The task with 870days will be ranked no1.
2nd Ranking.
Want to apply a ranking for each manager in column L across their respected region. Example.
5 PM's for region London. The PM's with the oldest o/s task will be ranked 1.
3rd Ranking
Want to apply a ranking for each manager in column M across the whole Region . I.e London, Harlow, Regions. Example
25 PM's for all regions. The PM's with the oldest o/s task will be ranked 1.
Only apply the ranking on Status 'overdue' in Column F. I tried to do all this with conventional excel formula's but it massively slowed down the file in terms of calculating.
sample (2).zip
-
Feb 2nd, 2021, 03:50 AM
#2
Re: Apply Ranking
this looks quite complex, i think if i was to be doing it i would look at using the worksheet as a database table
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Feb 2nd, 2021, 04:33 AM
#3
Thread Starter
Addicted Member
Re: Apply Ranking
I see. So can't be done by the normal way?
-
Feb 2nd, 2021, 06:17 AM
#4
Re: Apply Ranking
So can't be done by the normal way?
what is normal?
there are probably several ways it can be done,
i was only suggesting using VBA code using ADO to work with recordsets, to be able to use sql queries to return subsets of data in order, using the required criterias for each ranking type
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|