String-like based Sort

Hi all,

I want to be able to sort performance form (W = win, D = draw, L = lose). So where longest string of W's (from right to left) is at top and longest string of L's (from right to left) is bottom.

format example would be WWWWWW (A1:A6) = W in each cell and so forth for the subsequent rows.

Sort seems to have the ability to work on the string of wins part by adding levels but I can't seem to do and anything extra for losses in the same sort with the desired outcome.

Maybe a solution is assigning some sort of value to the string and then sorting by that value, i'm not sure.

Any help appreciated.

Thanks

I think you need to add some more details to this. To start off with, is your data in cells A1:F1 rather than A1:A6? Will all 6 cells be filled, or might some of them be blanks? Will it always be the last 6 results, or might you have more?

Then you need to describe better what you mean by the "longest string of W's". If you have LDLDWW, is that to be considered a better or worse performance than WWWDLW?

It will help you to get a solution more quickly if you attach a sample workbook, and in there you can describe how the data is laid out and how you would like various strings to be interpreted.

Hope this helps.

Pete

Thanks for the reply Pete. Sorry for the lack of info, you're right I actually meant A1:F1. It will always be last 6 games and for example if a team has only played 4 games then cells A1:B1 would be blank.

The main objective is to get the longest string of wins (right to left) on top and the longest string of losses (right to left) on bottom anything inbetween i'm not majorly concerned about. I've attached a small sample in an order I would expect the outcome to be.

In your example, LDLDWW would be better than WWWDLW as from right to left there is 2 wins as opposed to 1 win, I guess the key word is 'consecutive' wins (at top) and losses (at bottom), again working from right to left.

Hope this makes thing clearer.

Please find attachment, many thanks

Okay, put this formula in, say, H1 and then copy it down. It will join the 6 results together in reverse order (putting a space instead of an empty value), and then allocate a number for each letter - 3 for W, 2 for D, 1 for L and 0 for space:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(F1=""," ",F1)&IF(E1=""," ",E1)&IF(D1=""," ",D1)&IF(C1=""," ",C1)&IF(B1=""," ",B1)&IF(A1=""," ",A1),"W","3"),"D","2"),"L","1")," ","0")

Then you can include this column within your sort range and sort by this column, using Z to A order.

If you want the values from the formula to be actual numbers, you can multiply by 1, like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(F1=""," ",F1)&IF(E1=""," ",E1)&IF(D1=""," ",D1)&IF(C1=""," ",C1)&IF(B1=""," ",B1)&IF(A1=""," ",A1),"W","3"),"D","2"),"L","1")," ","0")*1

And then you can sort using the order of Largest to smallest.

Hope this helps.

Pete

Thanks Pete, this works perfectly. FYI - for the purpose of this, i'm treating having not played as better than a draw or loss so i've changed values to W = 3, no game = 2, D = 1 and L = 0.

Cheers
Anush

Okay, glad to be able to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete