Data sort on 2 columns incorrect on one line

[thadacto]

Good day all.

I have a spread sheet of 1319 lines and 15 columns wide, the first 3 lines are headers and split pane frozen.

When I data sort columns K are primary sort column in ascending order and secondary sort column J also in ascending order, there is one row (that I have noticed) that does not sort correctly in the secondary column.

If a sort is done on column J only, it is correct.

I have changed the name in the secondary column to xxxxxxx but that one row still does not sort correctly.

Anyone any ideas what is preventing the data sort?

Excel sorting error.jpg

I have just rewritten a new row with the data, deleted the original row and still the same problem.

[ajryan88]

Hi,

Could you please upload your workbook for me to investigate this issue?

Thanks

[thadacto]

Thanks for the help, ajryan88.

Errr, how do I upload a work book?

Thanks.

[ajryan88]

No worries.

Click on Reply --> Go Advanced --> Manage Attachments

[Pete_UK]

While you are composing a post, click on Go Advanced just below the Edit Window. From there, scroll down to Manage Attachments, then click on Add Files | Select Files and navigate to where the file is on your PC and double-click the file icon. Then click on Upload Files, then Done, and finally, once you have finished composing your post, click on Submit Reply.

Hope this helps.

Pete

[thadacto]

I Think I've attached the file!

Yes, Got it!

The particular row that is out of order is 764

[thadacto]

Thanks, Pete; yep, I got it.

But how does one upload and an avatar? I couldn't find any help on that.

[popipipo]

K765:K858 has spaces at the end of 'O'Neill Power Esq'


'O'Neill Power Esq' is not equal to 'O'Neill Power Esq '
That is why it goes wrong

[thadacto]

Thanks, Pete; yep, I got it.

[Pete_UK]

But how does one upload and an avatar? I couldn't find any help on that.

You have to make a certain number of posts before you can upload an avatar - I think it is 100 posts.

Pete

[ajryan88]

Hi,

K765:K774 contains a redundant space at the end of the cell, and K775:K858 contains a "." and a redundant space at the end of the cell.

So the sort is working perfectly, but the cells don't contain the same last name (although it may appear as though they do).

I would recommend trimming all cells on the worksheet to remove any other redundant spaces and give it another go:

Please Login or Register  to view this content.

Hope this helps

[thadacto]

Thanks, Willem; that correction has cured the problem. Well spotted. I have worked on it for over 4 hours and couldn't find the answer!

Thanks, Pete, re the avatar.

The site seems to hide things away in places you don't expect to find them!

[ajryan88]

Please don't forget to mark this thread as solved, and please click on the * next to any post(s) that have been helpful to you

Have a great day

[thadacto]

Many thanks to you also, ajryan88.
I've now run TRIM for the whole range.