0

I'm struggling to correct an issue in a data set where I am trying to sort rows by two different columns, but some rows contain a blank cell. I am sorting by State and then by Company; however, it seems that the existence of a blank cell in the column in between is causing the corresponding rows to jump to the top of their grouping despite not being included in the sort selection.

enter image description here

Even when I enter dummy names into the blank cells, I still end up with the same improperly sorted list. Am I doing something wrong here? Is there a common workaround for this issue?

For more context: I copy/paste these values from a Word doc and used text-to-column to separate the values.

4
  • 1
    despite not being included in the sort selection they ARE still part of the selection even if you don't choose to add them to sort
    – PeterH
    Commented Mar 31, 2020 at 19:22
  • Have you tried to copy these data to a new blank worksheet and kept values? I test on Office 365, the picture shows the sort result and the settings of sort levels .
    – Emily
    Commented Apr 1, 2020 at 6:04
  • Excel seems to be doing what you instruct it to do. Otherwise, would you mind posting your expected result, different from what you get? Commented Apr 1, 2020 at 9:29
  • The result posted by @EmilyHua is what I expected, as I tried to only sort by State (A-to-Z) and then by Company (A-to-Z). I expected both of those columns to be sorted alphabetically regardless of the value of Local Dist. Utility since I did not enter that column as a sort level, but I had not tried copying the data to a new blank sheet.
    – kh7
    Commented Apr 1, 2020 at 19:25

2 Answers 2

0

It is working correctly per how you instructed it to.

Blank sorts before text

The blank cell in the Utility column and the row below are first sorted by Arizona, and blank cells sort before text, which is why it is above Arizona Public Service

enter image description here

0

There would seem to be three main possible causes of what you see. The first, you causing it and lying here seems ridiculous so let's call it two possible causes.

The first of them is you sorting the information and using the LDU column as a column to sort by. In one of the comments you say that has not been done and there's nothing in your question to suggest otherwise. However, it could be happening before the formal sort you are talking about and then not really thought of as a cause.

Excel does not sort a column AT ALL if it is not listed as one to sort by. But several ways exist for the information to be in sorted order. How it was obtained, for example, by using lookups or FILTER() to populate a data set, then turning the data to values via copy and paste special, might include a sort either directly in the data's source or indirectly in how the formulas did the collection of data. Probably not here though as your question suggests direct importation.

But often work is done on imported data. And sorting is done to help the work. Then one does a final sort for storage or presentation. Excel does not sort on unspecified columns so it would NOT undo the earlier sorts in any way. If someone sorted A-z on that column earlier in its lifetime, it would still be organized as so until something undid the ordering that produced. Then it looks like it was part of YOUR sort because you perceive the order is the result of A sort, but in reality it was due to some earlier sort.

In keeping with that idea, but outside the Excel work, hence my considering it to be a third category (second of the two I mentioned addressing) is the data was sorted in its source. Its source to YOU or even earlier in its lifetime. So if it were sorted in WORD, or even before it was placed in Word, and no sorting there, or by you, made an active move to "undo" that sort, it would have been sorted when placed in Excel. Then this sort, which did not affect that, made it "float to the surface" and be an obvious pattern to you. But it was not the sort you mention's "fault": it existed before that sort and your sort just brought it to light, restored it to a situation that made it clear it had at one time been sorted like that.

And that seems like the source of the confusion here.

Further, odds are that if you did editing before this sort, you might find cells that seem out of order if you wander down through the data. Let's say you'd worked on this data for a while and one thing you did was the first line: you added an LDU so insted of the blank we see in the pic, maybe it now readso "Tucson Electric." Well, that would have conflicted with thinking the column had ever been a sorted thing and you might never have asked this question. So instead we see the top of the column and it looks sorted, but if examined a few hundred more rows, you might find dozens that seem out of order. They had been sorted before they were imported, but with blanks replaced by actual data, they no longer look like it. But you only noticed the top few rows before reaching the natural conclusion so...

If so, you have no problem, and nothing odd is happening. Happy day! And it DOES seem the likeliest thing.

You must log in to answer this question.

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