Breaking ties with rank alphabetically

**ktms** · 11-03-2011, 07:57 AM

hello,

I have a workbook in which I have a statistic sheet that uses the rank function to return the top 4 students in a number of categories.

The ranks themselves are okay, but I also have the students name being referenced.

This becomes a problem because in the event of a tie, it returns the same students name for each instance.

Is there a way to have the names of each student involved in the tie listed alphabetically rather than have the same student listed each time?

I have attached the workbook, since im sure little of what I explained makes sense..

Thanks a lot for any help!

**JBeaucaire** · 11-03-2011, 08:20 AM

The password is...?

**ktms** · 11-03-2011, 08:23 AM

I guess the password would be step one in getting help....

it is: CTEWELDING

sorry about that!

**NBVC** · 11-03-2011, 08:28 AM

In B4 enter formula:

Please Login or Register  to view this content.

hold the CTRL and SHIFT keys down and press ENTER. Then copy down.

Similar for the other sections, just changing the respective ranges.

**ktms** · 11-03-2011, 08:39 AM

Thank you! Exactly what I was looking for.

Could you please give be a brief explanation so I can apply this to the other blocks on that sheet?

Thanks again!!

**NBVC** · 11-03-2011, 09:05 AM

Well, the SMALL() here replaces the MATCH() to find the position number to INDEX() against...

SMALL(IF(Points!$B$4:$B$90=C4,ROW(Points!$B$4:$B$90)-ROW(Points!$B$4)+1),COUNTIF(C$4:C4,C4))

So we check if any of Points!B4:B90 matches value in C4, and if so we return the Row number for the position(s). This part ROW(Points!$B$4:$B$90)-ROW(Points!$B$4)+1 simply re-aligns the row count from 4:90 to 1:86 so that INDEX can use an actual position number starting from the natural 1.

This part: COUNTIF(C$4:C4,C4) is the k factor for the SMALL. As you copy the formula down, it counts duplicates and uses that to determine the next smallest row number to extract from, which aligns to next match to C4....

All you have to do in is change the Points!$B$4:$B$90 part to reference column of interest to find matches to... and change the C4's to match the first LARGE() result cell of the block.

Then you have to confirm the formula with CTRL+SHIFT+ENTER since it is an ARRAY formula... and copy down.

**mkenaw** · 11-03-2011, 07:00 PM

I coach a little league baseball team and over the years have added on to my stats file. I do a sports article after each game listing leaders in the various stat categories and with the most recent season over, I'd like to make my life easier going foward and just have that info listed out for me.

At issue is the LARGE / SMALL function ... there are various instances where more than one player has '8 hits' for example.

Is there an easy way to list each player's name instead of showing the first instance player's name multiple times?

Thanks
Matt

**NBVC** · 11-03-2011, 07:06 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.