Ranking based on text

G

gmackk

New Member
Joined
Nov 1, 2018
Messages
9
Dear members,

My colleagues and I work together on projects each quarter. Each quarter there is one project Lead, and the rest of us (depending on our availability) are either a Support (i.e. we provide assistance) or else we're Passive (i.e. we're not really involved at all).

Below is an example of the table which we have on file, and which we use to determine who is next in line to be project Lead (e.g. a quick scan across from left to right tells us that "Elaine" is long overdue to be a Lead (having last been one in Q2-16).

What I would find very useful would be to have a league table, or some form of way of ranking who has been "Lead", "Support" and "Passive", most often.

I realize I'm probably just creating work for myself but I would welcome any suggestions you may have of making this task simpler. I am using Excel 2010

Greatly appreciate any suggestions
Gavin

NameQ1-16Q2-16Q3-16Q4-16Q1-17Q2-17Q3-17Q4-17Q1-18Q2-18Q3-18Q4-18
JohnLeadSupportPassiveSupportPassiveSupportSupportPassiveSupportLeadSupportPassive
MarySupportSupportSupportLeadSupportPassiveSupportPassiveSupportPassiveLeadSupport
BenSupportPassiveSupportPassiveLeadSupportPassiveSupportPassiveSupportPassivePassive
JimSupportPassiveSupportSupportPassivePassivePassiveLeadSupportPassiveSupportLead
ElainePassiveLeadPassivePassiveSupportPassivePassiveSupportSupportSupportPassiveSupport
PaulPassivePassivePassivePassivePassiveLeadPassiveSupportPassiveSupportPassivePassive
LisaPassiveSupportLeadPassiveSupportPassivePassivePassivePassivePassiveSupportPassive
KellySupportPassivePassiveSupportSupportPassiveLeadPassivePassiveSupportPassiveSupport
PatSupportSupportSupportSupportPassivePassivePassivePassiveLeadSupportPassivePassive

<tbody>
</tbody>
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Hi & welcome to MrExcel.
mayb


Excel 2013/2016
ABCDEFGHIJKLM
1NameQ1-16Q2-16Q3-16Q4-16Q1-17Q2-17Q3-17Q4-17Q1-18Q2-18Q3-18Q4-18
2JohnLeadSupportPassiveSupportPassiveSupportSupportPassiveSupportLeadSupportPassive
3MarySupportSupportSupportLeadSupportPassiveSupportPassiveSupportPassiveLeadSupport
4BenSupportPassiveSupportPassiveLeadSupportPassiveSupportPassiveSupportPassivePassive
5JimSupportPassiveSupportSupportPassivePassivePassiveLeadSupportPassiveSupportLead
6ElainePassiveLeadPassivePassiveSupportPassivePassiveSupportSupportSupportPassiveSupport
7PaulPassivePassivePassivePassivePassiveLeadPassiveSupportPassiveSupportPassivePassive
8LisaPassiveSupportLeadPassiveSupportPassivePassivePassivePassivePassiveSupportPassive
9KellySupportPassivePassiveSupportSupportPassiveLeadPassivePassiveSupportPassiveSupport
10PatSupportSupportSupportSupportPassivePassivePassivePassiveLeadSupportPassivePassive
11
12
13NameLeadSupportPassive
14John264
15Mary273
16Ben156
17Jim255
18Elaine156
19Paul129
20Lisa138
21Kelly156
22Pat156
Sheet3
Cell Formulas
RangeFormula
B14=COUNTIF(INDEX($A$1:M$10,MATCH($A14,$A$1:$A$10,0),0),B$13)
C14=COUNTIF(INDEX($A$1:N$10,MATCH($A14,$A$1:$A$10,0),0),C$13)
D14=COUNTIF(INDEX($A$1:O$10,MATCH($A14,$A$1:$A$10,0),0),D$13)
 
Upvote 0
Thanks for the quick reply Fluff, greatly appreciated!

Is there any way that the "Name" column would automatically update to show the person with the greatest number of Leads, Support, Passive? I realize that I will need to have three separate tables to show who has had each of the roles most often
 
Upvote 0
Given your table what would you like to see?
 
Upvote 0
Thanks Fluff.

I would like to automatically sort (largest to smallest) by Lead, then by Support, then by Passive. E.g If I changed any of the role types in columns B:N the table in A13:D22 would automatically update so that the person with the highest numbers of Lead roles would move to the top. Grateful for your help with this.
 
Upvote 0
Unfortunately I don't know how to do that, so cannot offer any further help.
 
Upvote 0
If you are able to use "VBA" then Try this:-

This code will provide results starting "A13", Each time you select cell "A1".

To load Code:- Right click sheet Tab, Select "View Code", Vb window appears.
Paste the code below into the Vbwindow.
Close Vbwindow.

NB:- Each repeated selection of "A1" will Sort each subsequent column of the Results From Headings "Lead", "Support" to "Passive"
NB:- The Heading of the Currently sorted column will show VBYellow".

Code: 
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRay [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Static Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
Num = Num + 1
[COLOR="Navy"]Set[/COLOR] Rng = Cells(1).CurrentRegion
ReDim Ray(1 To Rng.Rows.Count + 1, 1 To 4)
Ray(1, 1) = "Name": Ray(1, 2) = "Lead": Ray(1, 3) = "Support": Ray(1, 4) = "Passive"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Columns(1).Offset(1).Cells
    c = c + 1
    Ray(c, 1) = Dn.Value
    Ray(c, 2) = Application.CountIf(Dn.Offset(, 1).Resize(, Rng.Columns.Count), "Lead")
    Ray(c, 3) = Application.CountIf(Dn.Offset(, 1).Resize(, Rng.Columns.Count), "Support")
    Ray(c, 4) = Application.CountIf(Dn.Offset(, 1).Resize(, Rng.Columns.Count), "Passive")
[COLOR="Navy"]Next[/COLOR] Dn

[COLOR="Navy"]With[/COLOR] Range("A13").Resize(c - 1, 4)
    Range("A13").Resize(, 4).Interior.Color = xlNone
   .Value = Ray
   .Sort key1:=Range("A13").Offset(, Num), Header:=xlYes, Order1:=xlDescending
    Range("A13").Offset(, Num).Interior.Color = vbYellow
    Num = IIf(Num = 3, 0, Num)
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick, that was very helpful. In the end I just recorded a macro by using custom sort, and then having three levels of sorting (i.e. first by Lead, then Support, then Passive).
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Copy values from one workbook to a another specific workbook based off a cell value
Replies
1
Views
428
thechad
T
A
  • Solved
Sum values based on condition, multiple conditions
Replies
2
Views
288
Peter_SSs
Peter_SSs
S
  • Solved
Rank the data
Replies
8
Views
394
sksanjeev786
S
L
  • Question
Insert table from one of 4 sheets into master invoice based on dropdown
Replies
0
Views
314
lissajo64
L
M
  • Question
Lookup based on Person in top row
Replies
1
Views
96
awoohaw
awoohaw

Forum statistics

Threads
1,215,335
Messages
6,124,326
Members
449,155
Latest member
ravioli44

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top