Ranking based on text

[gmackk]

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

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Q1-16[/TD]
[TD]Q2-16[/TD]
[TD]Q3-16[/TD]
[TD]Q4-16[/TD]
[TD]Q1-17[/TD]
[TD]Q2-17[/TD]
[TD]Q3-17[/TD]
[TD]Q4-17[/TD]
[TD]Q1-18[/TD]
[TD]Q2-18[/TD]
[TD]Q3-18[/TD]
[TD]Q4-18[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Lead[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Lead[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Lead[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Lead[/TD]
[TD]Support[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Lead[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Lead[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Lead[/TD]
[/TR]
[TR]
[TD]Elaine[/TD]
[TD]Passive[/TD]
[TD]Lead[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Lead[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[/TR]
[TR]
[TD]Lisa[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Lead[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Lead[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Support[/TD]
[/TR]
[TR]
[TD]Pat[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[TD]Lead[/TD]
[TD]Support[/TD]
[TD]Passive[/TD]
[TD]Passive[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
mayb

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Q1-16	Q2-16	Q3-16	Q4-16	Q1-17	Q2-17	Q3-17	Q4-17	Q1-18	Q2-18	Q3-18	Q4-18
2	John	Lead	Support	Passive	Support	Passive	Support	Support	Passive	Support	Lead	Support	Passive
3	Mary	Support	Support	Support	Lead	Support	Passive	Support	Passive	Support	Passive	Lead	Support
4	Ben	Support	Passive	Support	Passive	Lead	Support	Passive	Support	Passive	Support	Passive	Passive
5	Jim	Support	Passive	Support	Support	Passive	Passive	Passive	Lead	Support	Passive	Support	Lead
6	Elaine	Passive	Lead	Passive	Passive	Support	Passive	Passive	Support	Support	Support	Passive	Support
7	Paul	Passive	Passive	Passive	Passive	Passive	Lead	Passive	Support	Passive	Support	Passive	Passive
8	Lisa	Passive	Support	Lead	Passive	Support	Passive	Passive	Passive	Passive	Passive	Support	Passive
9	Kelly	Support	Passive	Passive	Support	Support	Passive	Lead	Passive	Passive	Support	Passive	Support
10	Pat	Support	Support	Support	Support	Passive	Passive	Passive	Passive	Lead	Support	Passive	Passive
11
12
13	Name	Lead	Support	Passive
14	John	2	6	4
15	Mary	2	7	3
16	Ben	1	5	6
17	Jim	2	5	5
18	Elaine	1	5	6
19	Paul	1	2	9
20	Lisa	1	3	8
21	Kelly	1	5	6
22	Pat	1	5	6
Sheet3

Cell Formulas
Range	Formula
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)

 

[gmackk]

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

 

[Fluff]

Given your table what would you like to see?

 

[gmackk]

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.

 

[Fluff]

Unfortunately I don't know how to do that, so cannot offer any further help.

 

[gmackk]

No problem Fluff - thanks for your help! Much appreciated

 

[Fluff]

You're welcome

 

[MickG]

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".

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

 

[gmackk]

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).