Ranking Alphabetically
Ranking Alphabetically
(OP)
Hey fellow geniuses,
Just wondering if there is a way to rank in alphabetical order, a list of names but only if a value in an adjacent cell is greater than 1?
On the attached worksheet, I would like to rank the names that have a value in row B greater than one, while ignoring the names that have “0” or are blank.
Cheers,
Just wondering if there is a way to rank in alphabetical order, a list of names but only if a value in an adjacent cell is greater than 1?
On the attached worksheet, I would like to rank the names that have a value in row B greater than one, while ignoring the names that have “0” or are blank.
Cheers,





RE: Ranking Alphabetically
In cell I7 enter: =IF(B7<1,"zzzz","")&A7 and copy down
This will add "zzzz" to the front of any name with no events played.
Then in cell D7 enter: =IF(B7>=1,COUNTIF($I$7:$I$11,"<"&I7)+1,"") and copy down.
This will rank the names in column I, and return just the rank for those with a value >=1 in column B.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Ranking Alphabetically
First you state, that the criteria for the ranking is "only if a value in an adjacent cell is greater than 1".
Then you state that the criteria for the ranking is "ignoring the names that have “0” or are blank".
Finally, your working example has no value of 1 in Events Played.
So by the preponderance of evidence, it could maybe perhaps be abducted that you actually meant to state something that you actually did not or not.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Ranking Alphabetically
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Ranking Alphabetically
Assuming you add some stat in those columns that you may want to sort by at some point I suggest formatting the entire table as a "table". On the HOME ribbon, select Format as Table, then select one of the color schemes. On the next Window be sure to check the box labeled "My table has headers".
That will give you the smart filters IRStuff is referring to. As you add people and stats you can apply filters to multiple columns and sort by any column you want.
RE: Ranking Alphabetically
Having transformed your table to a Structured Table, there are a plethora of amazing features that can be accessed by a new, context sensitive tool bar.
If you don't leverage this exciting feature, it would be like only using your Porsche to drive to the corner store down the block, on Mondays.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Ranking Alphabetically
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Ranking Alphabetically
However, just try adding a row of data to your table. NOW your formula will not include the additional row, UNLESS you have a Structured Table!
What the Structured Table feature does for you is 1) automatically adds the formula to the new table row and 2) automatically adjusts the column range referenced in the formula, NEITHER of which will happen in a non Structured Table.
The first benefit solves the negative issue that I have seen so many times, where a user pre-fills unused rows of data in a table with formulas, which is never a best and accepted practice.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Ranking Alphabetically
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Ranking Alphabetically
D7: =IF([@[Events Played]]>=1,COUNTIF([Name],"<"&[@Name])+1,0)
...or with a different aggregator, which I prefer...
D7: =IF([@[Events Played]]>=1,SUMPRODUCT(--([Name]<[@Name]))+1,0)
Skip,
for a NUance!
RE: Ranking Alphabetically
Why the preference for Sumproduct over Countif?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Ranking Alphabetically
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Ranking Alphabetically
...this
(a=b)
(a<b)
...versus...
(a,b)
(a,"<"&b)
The former is much more intuitive to me, especially with multiple criteria.
Skip,
for a NUance!
RE: Ranking Alphabetically
In a structured table, to achieve the results you are looking for:
After converting your spreadsheet to use a structured table the results you are looking for can be done in 4 easy, quick steps.
1) Click the Events Played Pulldown arrow and Sort Largest to Smallest (For some reason if you skip this step, the end result will not be the same.)
2) Click the Events Played Pulldown arrow and deselect "0" in filters leaving only rows with a positive number in this column.
3) Click the Names Pulldown and Sort A to Z
4) Click the Events Played Pulldown arrow and select "0" in filters (all numbers available now)
Viola!
I've taken the liberty of adding win/loss and avgerage stats to give you something to look at and play with. Give you an idea(s) of what you can do.
To add names/people, just enter the name and relavent stats in the row directly below the table. It will automatically be added to the table, no need to "ADD" rows to the table first.
PS) I did add a couple new names, and the win/loss stats I pulled out of thin air.
BTW, where is the OP, I'm not seeing any replies from him. Are we answering his question satisfactorily?
RE: Ranking Alphabetically
- It provides the alphabetical rank number, which is what the OP asked for
- You can update the number of events played for any player and the rank updates automatically
- You can sort on the modified name column in a single operation
- You can insert new rows and the modified name and rank formulas are generated automatically.
Spreadsheet with formulas attached (using SumProduct, to keep Skip happy :))
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Ranking Alphabetically
Skip,
Just traded in my OLD subtlety...
for a NUance!