Sorteren combinatie letters, cijfesr en tekens

[AnnevdD]

In een spreadsheet wil ik een aantal gegevens sorteren bestaande uit letters, cijfers en speciale tekens.
Dat ging altijd goed, maar sinds gisteren heb ik een probleem met teksten waarin een -teken is opgenomen.
Hierbij blijkt de tekst "eh-" kleiner te zijn dan "ehb" maar de tekst "ehb" is kleiner dan "eh-r". De sorteer volgorde wordt dan "eh-", "ehb", "eh-r".
Volgens mij klopt dat niet en was dat voorheen ook niet zo.

Zie ook het bijgaande bestandje.

Kan iemand me helpen?
Bekijk bijlage sorteertestje.xlsx

 

[Ad Becude]

Kijk eens naar het voorbeeldje, zo krijg je het zelfde resultaat.

 

[Piet Bom]

Er is iets vreemds aan de hand (ik werk met Excel2010)
Ik heb het min-teken vervangen door een laag liggend streepje en dan sorteert hij wel volgens verwachting.
Ik heb een vermoeden dat Excel het min-teken interpreteerd als een operator of een separator i.p.v. een tekst-teken.

 

[plongske]

@ piet
Als je de underscore toepast in kolom A in de oplossingsvoorbeeld van Ad; Welke scores krijg je dan in de gele cellen?
De code van - = 45 die van de _ is 95 maar het resultaat blijft toch gelijk?
Het probleem lijkt mij dat er 3 karakters met 4 karakters wordt vergeleken.

 

[AnnevdD]

Ik denk dat er echt iets vreemds gebeurd. Ik heb Excel2016. Ik doe al jaren af en toe een vergelijking van directory listings die ik eerst in Excel inlees, vervolgens sorteer en dan met een macro vergelijk.

Kort geleden bleek dat dat niet goed gaat als er een "-" in de bestandsnaam staat. Dat was de achtergrond van mijn vraag.

Ik heb n.a.v. de opmerkingen van Ad in het bestandje een testje gedaan (kolom L tot S) van de vergelijking van twee velden met formules in Excel én met een macro. Dat blijkt een verschillend resultaat op te leveren bij de verhouding van "ehb3" en "eh-r". Dat kan toch niet?
Ik heb vervolgens het "- teken" vervangen door een "0" (kolom u tot AA). Dan zijn de verhoudingen wel goed.

Volgens mij behoort tekst sorteren ook niet afhankelijk te zijn van de som van de waarde van de tekens. In een tekst maar wordt er teken voor teken gesorteerd.

Het probleem is m.i. dat het sorteren met een "-" teken in de tekst een afwijkend resultaat geeft. Je kunt dat in mijn bestandje zien door in cel U2 verschillende speciale tekens in te voeren.

Het vergelijken van twee teksten waarin een "-"teken staat levert een verschillend resultaat op in de werkblad formules en in de macro.
Kan dat iets met instellingen te maken hebben?

 

[plongske]

Zal Piet toch nog gelijk hebben.
Even verschillende tekens ingevoerd en blijkt enkel bij het - teken fout te lopen.

 

[Piet Bom]

Ik zal ff een mailtje sturen naar Bill Gates, dat hij zijn Excel-tooltje ff fixed.
Voor 0,1% van zijn vermogen zal ik hem wat tips geven

 

[sylvester-ponte]

als je "Option Compare Binary" gebruikt dan doet de macro het goed

 

[snb]

Hier niet. Overigens is binary default.

 

[sylvester-ponte]

als je zelf een sorteermacro maakt en de "Option Compare Binary" instelt werkt het zoals je zelf wil.

later voeg ik een voorbeeldje toe.

 

[sylvester-ponte]

hier het voorbeeldje: het gaat niet om het programmaatje maar om de compare_mode

als je in module1 de "option compare text" weg haalt gaat het sorteren goed

weet iemand hoe deze mode vanuit vba is aan te passen?

 

[rebmog]

Het afwijkende sorteergedrag van een min-teken is geen bug, maar is by design. Vraag me niet waarom, maar min-tekens en apostrofs worden standaard genegeerd bij het sorteren. Zie: https://support.microsoft.com/en-us/help/322067/how-to-correctly-sort-alphanumeric-data-in-excel

 

[Piet Bom]

Goed gevonden Sylvester en Rebmog !
Zonder jullie bevindingen zou ik nooit van dit probleem bewust zijn geworden.
En heb nu zelfs een methode om er goed mee om te gaan.
Ook de TS (AnnevdD) bedankt voor het melden van de constatering van de sorteerfout.

 

[AnnevdD]

Sylvester-ponte, rebmog, Piet en anderen bedankt voor jullie inbreng.
Ik heb nog wat zitten testen met mijn macro en kom tot de volgende bevindingen:

• als ik option Compare Binary inschakel wordt de sorteervolgorde beter, maar ontstaat er en verschil tussen de volgorde volgens de sort functie en de tekst vergelijkingsfunctie >, = en <.
  Dat levert bij mijn toepassing (een soort balanced line) fouten op.
• als ik option Compare Text inschakel dan is de sorteervolgorde minder correct, maar ontstaat er geen verschil met de resultaten van de vergelijkingsfunctie.
  Dat laatste is voor mij belangrijk, dus gebruik ik n u option compaer text en werkt het weer als vanouds.

Overigens denk ik zeker te weten dat dit min-teken probleem recent (laatste jaar of zo) is ontstaan. Want ik gebruik deze balanced line functie al heel wat jaren af en toe. En dit was me niet eerder overkomen.

Als iemand geïnteresseerd is in die functie en de daarbij behorende resultaten dan wil ik die wel een keer (ws volgende week) in een gestroomlijnde vorm uploaden.