Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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. "Patlee" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
You have responses at your other posts.
Patlee wrote: 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? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
Unfortunately that's not the reason -- all formatting is the same. But
thanks for responding. "Joel" wrote: 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. "Patlee" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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 wrote: Unfortunately that's not the reason -- all formatting is the same. But thanks for responding. "Joel" wrote: 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. "Patlee" wrote: 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? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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" wrote: 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 wrote: Unfortunately that's not the reason -- all formatting is the same. But thanks for responding. "Joel" wrote: 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. "Patlee" wrote: 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? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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? Patlee wrote: 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" wrote: 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 wrote: Unfortunately that's not the reason -- all formatting is the same. But thanks for responding. "Joel" wrote: 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. "Patlee" wrote: 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? -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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. "Dave Peterson" wrote: You have responses at your other posts. Patlee wrote: 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? -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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" wrote: 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? Patlee wrote: 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" wrote: 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 wrote: Unfortunately that's not the reason -- all formatting is the same. But thanks for responding. "Joel" wrote: 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. "Patlee" wrote: 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? -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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 On Sat, 26 May 2007 08:54:00 -0700, Joel wrote: 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. "Dave Peterson" wrote: You have responses at your other posts. Patlee wrote: 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? -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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 Patlee wrote: 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" wrote: 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? Patlee wrote: 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" wrote: 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 wrote: Unfortunately that's not the reason -- all formatting is the same. But thanks for responding. "Joel" wrote: 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. "Patlee" wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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. Joel wrote: 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. "Dave Peterson" wrote: You have responses at your other posts. Patlee wrote: 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? -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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" wrote: 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. Joel wrote: 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. "Dave Peterson" wrote: You have responses at your other posts. Patlee wrote: 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? -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting is Incomplete
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. Patlee wrote: 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" wrote: 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. Joel wrote: 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. "Dave Peterson" wrote: You have responses at your other posts. Patlee wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
oldest incomplete job | Excel Worksheet Functions | |||
Incomplete List of Fonts for Symbols? | Excel Discussion (Misc queries) | |||
Access import query list incomplete | Excel Worksheet Functions | |||
Axis values showing incomplete | Charts and Charting in Excel | |||
Mail Merge incomplete | New Users to Excel |