1

I have a very simple list. Column A contains a list of movies and Column B contains their status, either "Watched" or "Not Watched". I want a list sorted alphabetically by movie name. When I click the Sort A to Z button(with active cell in column A), it gives me a alphabetical list of Watched movies first and then an alphabetical list of Not Watched movies. Exactly like if I did 2 level sorting, first by name and then status. Why is this? How can I get a list sorted only by movie name regardless of if it's status?

4
  • I am not able to reproduce the issue; it works like you want it for me. do you have any macros or other fancy stuff in your sheet? Can you post a screencopy?
    – Aganju
    Aug 29, 2017 at 17:06
  • If I create a small, mock list then that works exactly like how I want it to and how it should. This leads me to conclude that there must be something in the data that must be causing this instead of some global settings. I don't have any active macros, no fancy stuff, just a very simple list. I doubt a screecopy will help so here's the link to the file: drive.google.com/open?id=0B4uFbvcRQ3GYWGZtUE9tVnc2M3c
    – Saad Amin
    Aug 29, 2017 at 17:19
  • 1
    Yes, this file does the same thing for me. I tried all kinds of things, but cannot identify the issue. Maybe it is supposed to be a feature. You certainly got me flabbergasted, I would have bet my arm that it's something you do wrong, but no, it is real.
    – Aganju
    Aug 29, 2017 at 21:25
  • 1
    Thanks for dedicating some of your time to helping me out. I found the problem, I've posted the answer below.
    – Saad Amin
    Aug 30, 2017 at 8:28

2 Answers 2

1

Figured it out. Turns out that when I copied the data from Notepad, it somehow messed up the data but not visually. Whenever I tried to edit a cell, the text would behave very weirdly. This time, I didn't use the import wizard so it went fine.

0

Just to clarify what you already found, when I copied the first 14 rows of your spreadsheet and pasted them into a text window (e.g., Notepad), I got this:

Movie Name      Status
"                                       10 Cloverfield Lane"    Watched
12 Angry Men    Not Watched
"                                       12 Monkeys"     Watched
"                                       12 Years a Slave"       Watched
"                                       10,000 BC"      Watched
"                                       2 Fast 2 Furious"       Watched
"                                       21 Jump Street" Watched
"                                       22 Jump Street  "       Watched
"                                       28 Days Later           "       Watched
"                                       71      "       Watched
"                                       300     "       Watched
"                                       300: Rise of an Empire" Watched
5 Centimeters Per Second        Not Watched

Every movie that you had watched had five Tab characters at the beginning of its name.  Every movie that you had not watched was as it appeared.  I got a hunch as to what was happening when I cleared Column B and still got the same results from sorting Column A — i.e., Excel wasn’t doing two-level sorting1; it was getting those results just by sorting Column A.  Then I entered =CODE(A2) into cell C2 and dragged down.  All the watched movies displayed 9 (the ASCII value of Tab); the unwatched movies displayed the ASCII value of the first character of their name (i.e., numbers ranging between 49 for 1 to 90 for Z).

I don’t know why Excel didn’t make the Tabs easier to detect (i.e., visible).
______________
1 By the way, you got it backwards in your question.  The effect you were getting was as if you were sorting first by status and then by name.

1
  • 1
    Yeah, I actually imported that list from notepad and thats how I kept it in notepad. Thanks for your help.
    – Saad Amin
    Aug 31, 2017 at 7:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .