Sorting is Incomplete

[Patlee]

Trying to sort 3 columns (last name-first name-birthday) by birthday. Excel
sorts correctly but only the first 82 rows. It leaves the last 20 rows
unsorted. How can I get it to sort the entire worksheet?

 

[Joel]

I suspect the last lines are not formated the same as the first lines. One
set of dates is probably text and the other set is microsoft date. Convert
all the dates to the same format.

 

[Dave Peterson]

You have responses at your other posts.

Trying to sort 3 columns (last name-first name-birthday) by birthday. Excel
sorts correctly but only the first 82 rows. It leaves the last 20 rows
unsorted. How can I get it to sort the entire worksheet?

 

[Patlee]

Unfortunately that's not the reason -- all formatting is the same. But
thanks for responding.

 

[Dave Peterson]

The number format of a cell isn't the deciding factor.

If you can locate one of those cells that doesn't sort, try this in an empty
cell:
=isnumber(x99)
replace x99 with the address of the offending cell.

If it comes back False, then the value isn't a date--it's just text.

Depending on what's in those cells, there may be a quick way to convert
them--but you didn't share any examples of what's in them.

 

[Patlee]

The cells are all birthdays in the column I am trying to sort (1/6 - 3/8 -
12/15, 6/20 - etc.) Excel sorts 82 birthdays correctly; that is, 1 through
12. However, on the 83rd line it starts the remaining 20 with 1/13 through
10/10 and then starts over again with 1/11 through 12/24. I really appreciate
your attempt to help.

 

[Dave Peterson]

And what was returned from that =isnumber() formula? I didn't see that answered
in your reply.

Try formatting those dates with a custom format of:
mmmm dd, yyyy

Do all the cells in that range display a long date?

How about the years? Are they what you expect?

 

[Joel]

Dave: I think his dates are text formated and that is why here is not getting
a good sort. He should change the dates to real dates and then the sort will
work.

 

[Patlee]

I'm out of my league here; i.e., I'm not computer literate enough to
understand your suggestions on how to determine if it's not a date but rather
it's text. I will say that the information in this column is all entered the
same. It's simply a birthdate, no year, just a date -- number of month (1
through 12), a slash, and then the day of the month (1 through 31). It's a
three column worksheet with last name, first name, birthday. The cells in
the birthday column that are not being included in the sort are no different
than the 82 that are sorting correctly.

 

[Gord Dibben]

I'm not defending Dave who has posted the same answer about 3 times to OP.

But............I will mention that you seem to have double-standard when it
comes to responding to posts that have been previously answered.

http://snipurl.com/1m8dn


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Did you try reformatting one of those cells with the offending dates?

Select all the cells in the column.
Format|Cells|number tab|custom category
type this in that Type: box
mmmm dd, yyyy

If any of the cells don't change what's displayed to a long date format (like
January 01, 2007), then those cells aren't really dates.

If this is what happens, then the next step is to share what is in those
cells--don't just say a date.

Copy and paste into your reply.

You'll get more responses if you show something like:

01-07
01/07
1/7
Jan 7

I'm out of my league here; i.e., I'm not computer literate enough to
understand your suggestions on how to determine if it's not a date but rather
it's text. I will say that the information in this column is all entered the
same. It's simply a birthdate, no year, just a date -- number of month (1
through 12), a slash, and then the day of the month (1 through 31). It's a
three column worksheet with last name, first name, birthday. The cells in
the birthday column that are not being included in the sort are no different
than the 82 that are sorting correctly.

 

[Dave Peterson]

Sounds like it based on the follow-ups from the OP.

But it could have been that the OP didn't include all the rows to sort, too. I
guessed that the problem would not have had the subject "incomplete".

I would have thought that even by the accident that the last 20 rows would have
sorted (as text) into a different sequence.

And sorting the first 82 rows as numbers and the last 20 as text isn't an
"incomplete" sort (in my book). It just sorts it in a manner that the OP
doesn't want.

 

[Patlee]

I fear I didn't do a good job describing the problem adequately. The "last
20 rows" that didn't sort were not the last 20 rows in my worsheet. They are
the last 20 rows that appear after sorting and they are random (not in
sequence in the worksheet nor by birthdate). They are scattered throughout
the worksheet and all were entered in the same manner and all 102 were
selected when performing the sort. But I do thank you for all time you have
spent on this.

 

[Dave Peterson]

You haven't answered any of the questions I asked.

Until you do that, I'm out of ideas.

Maybe someone else will can help.

I fear I didn't do a good job describing the problem adequately. The "last
20 rows" that didn't sort were not the last 20 rows in my worsheet. They are
the last 20 rows that appear after sorting and they are random (not in
sequence in the worksheet nor by birthdate). They are scattered throughout
the worksheet and all were entered in the same manner and all 102 were
selected when performing the sort. But I do thank you for all time you have
spent on this.