INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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,

RE: Ranking Alphabetically

Based on a reply here: https://www.mrexcel.com/forum/excel-questions/6222...

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Ranking Alphabetically

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.

RE: Ranking Alphabetically

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Ranking Alphabetically

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/

RE: Ranking Alphabetically

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Ranking Alphabetically

This is how Doug's formula would look in a Structured Table...

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Ranking Alphabetically

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/

RE: Ranking Alphabetically

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/

RE: Ranking Alphabetically

I like using actual equalities, without messing with COMMAS, QUOTATION MARKS and CONCATENATION...
...this

(a=b)
(a<b)

...versus...

(a,b)
(a,"<"&b)


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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Ranking Alphabetically

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?

RE: Ranking Alphabetically

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/

RE: Ranking Alphabetically

Ahhhhhhhhhh thanks

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close