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]

 

[MickG]

You're welcome