Forum Discussion

Paul_Castelino's avatar
Paul_Castelino
Copper Contributor
May 01, 2020

Irregular Sorting in Excel

I am having a very strange issue. The SORT result of a Data Column in an Excel file is incorrect when I sort using my Windos Laptop. However, when I do the same sorting function from another computer ...
excel
office 365
Paul_Castelino's avatar
Paul_Castelino
Copper Contributor
May 01, 2020
Data is in a Table format so the Sorting steps are correct. The issue is that ot does not sort in correct order from my Laptop but the same file sorts correctly from other devices.
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 01, 2020

Paul_Castelino 

If your laptop is on Windows did you have a chance to test on another Windows machine? Or perhaps you could submit the file in question here to remove sensitive information.

  • Paul_Castelino's avatar
    Paul_Castelino
    Copper Contributor
    May 02, 2020

    SergeiBaklanAttached is the file. In one column in the data sorted from another device and in another column is the file sorted from my laptop.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 02, 2020

      Paul_Castelino 

      Yes, as Detlef_Lewin mentioned there are about hundred texts with not-printable unicode characters in front. Excel on Windows uses unicode sorting and to my knowledge there is no option to shift on ANSII sorting and back. Not sure how Excel for Mac works, based on your sample it performs ANSII sorting.

      On Excel for Windows Power Query sorts correctly ignoring non-printable unicode characters.

      • Paul_Castelino's avatar
        Paul_Castelino
        Copper Contributor
        May 02, 2020

        SergeiBaklan 

        Detlef_Lewin 

         

        Tried entering the formula but its giving an error message.

        I hope you have the file. What corrections would I need to do?

         

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      May 02, 2020

      Paul_Castelino 

      Check at least the first three characters of each text:

      =UNICODE(MID($D2;1;1))
=UNICODE(MID($D2;2;1))
=UNICODE(MID($D2;3;1))

       

Resources