• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort list alphabetically - Text string too large, not working

C

cacos

Member
Hi everyone!


I'm struggling with an array to sort a list alphabetically (list is dynamic), I've tried virtually every forum related to this issue and all of the proposed ideas seem to fail when the text on some cells is rather large (looks like 255 is the maximum characters it can take?)


I'm using this array since there are duplicates on the list, and it respects that:


=INDEX(List,MATCH(SMALL(COUNTIF(List,"<"&List),ROW(1:1)),COUNTIF(List,"<"&List),0))


I've already checked chandoo.org/wp/2008/10/22/sorting-text-in-excel-using-formulas/


Thanks!
 
Hi, cacos!


Give a look at this:

http://support.microsoft.com/kb/231114


These amazingly surprising guys at Redmond say that this was an isse of 2000 version... maybe they missed a 1, I guess. Well, in fact that was an old article, I once read another that talked about 2007 version but I can't find it.


There are other MS links related like:

http://answers.microsoft.com/en-us/office/forum/office_2010-excel/using-countif-function-in-vba/67c575e1-d20b-46c8-af3d-70a75acd791f?auth=1


Regards!


PS: BTW, your uploaded file works with 254 chars and not with 255. Try changing "<" by "<=" and see what happens: you'll then have to reduce string lenght to 253.... so the 255 limit is for the whole expression criteria&criteria_value.
 
Hi SirJB7! Thank you, so the only way would be to build a macro? I've seen the article you wrote, i'm a total vba amateur. I'm not sure how to adapt this to the report i'm using
 
Hi, cacos!


With VBA you'd experience the same error if using Application.WorksheetFunction.CountIf since it runs the same internal process. If you dig thru similar links you'd notice several cases pointing to Excel functions and VBA issues too.


Regards!
 
Hi, cacos!

Glad to help you. Thanks for your feedback. And welcome back whenever needed or wanted.

Regards!
 
Hi ,


The problem is slightly different ; you are right that the sorting formulae fail when any of the text strings is longer than 254 characters ( 255 or more ) ; however , that is not a problem really , unless your text strings differ in a position which is greater than 254 , e.g. in the 300th position.


If you are sure that this is not the case , then changing your formula slightly , will still allow you to sort your rows :


=INDEX(List,MATCH(SMALL((COUNTIF(List,"<"&LEFT(List,254))),ROW(1:1)),COUNTIF(List,"<"&LEFT(List,254)),0))


entered as an array formula , using CTRL SHIFT ENTER , still works.


Narayan
 
You must log in or register to reply here.
Back
Top