Ranking Alphabetically 1

[iken]

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,

 

[IDS]

Based on a reply here:

https://www.mrexcel.com/forum/excel-questions/622243-ranking-strings.html

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/

 

[SkipVought]

Just a comment on your stated requirement.

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!}

 

[IRstuff]

You can use the autofilter function to display only those with column B with legitimate values and then do an alphabetic sort on the remainder

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[dbill74]

What are the remainnig columns going to contain?

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.

 

[SkipVought]

dbill74 is referring to the Structured Table feature, introduced in version 2007, that can be found in Insert > Tables > Table >> Create Table.

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!}

 

[IDS]

Structured tables may be an amazing feature, but it seems to me that if you want a ranking that updates automatically and doesn't require hiding and sorting, then a formula combined with a structured table is a better way to do it.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

It's really not an either or. Yes, absolutely! A formula updates immediately.

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!}

 

[IDS]

Skip - good point; I have edited my post!

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

This is how Doug's formula would look in a Structured Table...
[tt]
D7: =IF([@[Events Played]]>=1,COUNTIF([Name],"<"&[@Name])+1,0)
[/tt]

...or with a different [highlight #FCAF3E]aggregator[/highlight], which I [highlight #FCE94F]prefer[/highlight]...
[tt]
D7: =IF([@[Events Played]]>=1,[highlight #FCAF3E]SUMPRODUCT[/highlight](--([Name][highlight #FCE94F]<[/highlight][@Name]))+1,0)
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

Skip - Thanks for that. I should probably get more familiar with these new-fangled structured tables things

Why the preference for Sumproduct over Countif?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

Just thought of something else. You would need to include the modified name column in the table, and use that in the countif or sumproduct formula, to exclude the people with zero events played from the ranking.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

I like using actual equalities, without messing with COMMAS, QUOTATION MARKS and CONCATENATION...
...this
[tt]
(a=b)
(a<b)
[/tt]
...versus...
[tt]
(a,b)
(a,"<"&b)
[/tt]

The former is much more intuitive to me, especially with multiple criteria.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dbill74]

This is becoming a lot of work for something that can be done simply and easily and still leaves lots of options for future; this is the power of a structured table, see attached.

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?

 

[IDS]

dbill74 - Adding the modified name column and the rank formula doesn't take very long, it's a one-off operation, and it works well in conjunction with the structured table:

- 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/

 

[SkipVought]

Ahhhhhhhhhh

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}