# Rank by Wins, Alphabetically & Concatenate Results

• I have looked at many Ranking Q & A’s in the forum but can’t find a formula which quite answers what I am looking for. I have a results grid and wish to Rank Names by most wins using the following criteria with a helper column:

1. Most wins
2. If equal on Wins (least number of Losses but with equal rank number)
3. If still equal, alphabetically - A to Z

Note that even although Fred has more losses and is placed behind Chas & Dave he is still equal on position 2 and lastly the W/ L Results are concatenated

Thanks for looking and I hope I have explained it a satisfactorily.

## Files

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Try these:

in H2:

=LARGE(\$C\$2:\$C\$6,ROWS(H\$2:H2))&"-"&SMALL(\$D\$2:\$D\$6,ROWS(H\$2:H2))

copied down

In I2, we need a helper column (can be hidden):

=VLOOKUP(G2,\$B\$2:\$C\$6,2,0)

copied down

in G2:

=INDEX(\$B\$2:\$B\$6,SMALL(IF(\$C\$2:\$C\$6&"-"&\$D\$2:\$D\$6=H2,ROW(\$B\$2:\$B\$6)-ROW(\$B\$2)+1),COUNTIF(H\$2:H2,H2)))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

in F2:

=RANK(I2,I\$2:I\$6)

copied down

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Many thanks for your very prompt respone and although it works very well indeed it is not quite correct in that it doesn't seem to rank tied teams alphabetically as Dave should be listed above Fred although correctly shown with the same number under POS and I attach a copy to show you.

Also I should have said that sometimes our competitions vary in the number of teams taking part but it won't allow me to leave any "blank" cells in Col B, maybe this is because of the array formula. Also this area is a small part of a much larger application that officials use for scoring and again is it possible that by the of use say IF??="","", or iserror etc. so that where cells have no data in them they stay blank and that the #Num! does not appear. It's really for cosmetic purposes but it would make the app look "neater" if you see what I mean if it is possible.

But it's terrific what you have done and I really appreciate your help. Much Regards

## Files

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Hi weebilko,

It just dawned on me that I didn't respond to your follow-up. I apologize for that. I had noticed it on the weekend, but by the time Monday had come, I had forgotten....

Anyway, I think we will need a couple more helper columns to also sort the results.... please see attached.

To get rid of the errors, simply use the IFERROR() function wrapped around your formulas.

=IFERROR(LARGE(\$C\$2:\$C\$6,ROWS(H\$2:H2))&"-"&SMALL(\$D\$2:\$D\$6,ROWS(H\$2:H2)),"")

then the helper columns:

In I2:

=IFERROR(INDEX(\$B\$2:\$B\$6,SMALL(IF(\$C\$2:\$C\$6&"-"&\$D\$2:\$D\$6=H2,ROW(\$B\$2:\$B\$6)-ROW(\$B\$2)+1),COUNTIF(H\$2:H2,H2))),"") confirmed with CTRL+SHIFT+ENTER

in J2:

=IF(I2="","",COUNTIFS(\$H\$2:\$H\$6,H2,\$I\$2:\$I\$6,"<"&I2))

in K2:

=IFERROR(VLOOKUP(I2,\$B\$2:\$C\$6,2,0),"")

back to final formulas:

in F2:

=IFERROR(RANK(K2,K\$2:K\$6),"")

in G2:

=IFERROR(INDEX(\$I\$2:\$I\$6,MATCH(1,(\$H\$2:\$H\$6=H2)*(\$J\$2:\$J\$6=SMALL(IF(\$H\$2:\$H\$6=H2,\$J\$2:\$J\$6),COUNTIF(H\$2:H2,H2))),0)),"") confirmed with CTRL+SHIFT+ENTER

Copy these cells down.

## Files

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Many many thanks, terrific, that seems perfect I am so glad you came back to me so please no apologies it is thanks to you folks that give so much of your time to help us fix our problems

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

No problem... and thanks for the positive feedback

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Sorry, can you help? I wished to amend the Rank File that you created but I gues I have made an error and I cannot seem to find it so where have I gone wrong?

## Files

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

I think we will need to re-tool a couple of the formulas....

First, helper 3 in BW61, change it to:

=IFERROR(LARGE(\$BN\$61:\$BN\$72,ROWS(\$BW\$61:\$BW61)),"")

copied down.

Then change BT61 to:

=IFERROR(BW61&"-"&SMALL(IF(\$BN\$61:\$BN\$72=BW61,\$BO\$61:\$BO\$72),COUNTIF(\$BW\$61:\$BW61,\$BW61)),"")

this one needs CTRL+SHIFT+ENTER confirmation, then copied down.

The remainder of the formulas should be ok as they are.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

That's great many thanks again

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

I am sorry to bug you again have a last request, I hope, to add another col of numbers and I can't seem to find the correct formula so can you help? Would be so grateful.

## Files

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Try:

=INDEX(\$BL\$61:\$BL\$72,MATCH(BS61,\$BM\$61:\$BM\$72,0))

copied down

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Many thanks but have tried to add IFERROR but coudn't get it to work where the list is not always fully populated, sorry can you help.

## Files

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Try:

=IF(BS61="","",INDEX(\$BL\$61:\$BL\$72,MATCH(BS61,\$BM\$61:\$BM\$72,0)))

copied down.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Thanks but if the list is shortened there are still problems?

## Files

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

Are we talking about column BX? What exactly is the problem you are talking about?

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

If your are talking about column BT, then change formula again too:

=IFERROR(BW61&"-"&SMALL(IF(\$BN\$61:\$BN\$72=BW61,IF(\$BO\$61:\$BO\$72<>"",\$BO\$61:\$BO\$72)),COUNTIF(\$BW\$61:\$BW61,\$BW61)),"")

CSE confirmed and copied down.

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Re: Rank by Wins, Alphabetically &amp; Concatenate Results

That's it many thanks.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!