Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

Ranking Alphabetically 1

Status
Not open for further replies.
Replies continue below

Recommended for you

Sort by date Sort by votes
Upvote 0 Downvote
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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

sort_asinxe.gif


TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.
Back
Top