Hope it's OK to ask this he I am trying to sort a column of times 423PM,
958AM, 1034AM, 1245PM, etc.) but the sort puts the column this way - 1034AM,
1245PM, 423PM, 958AM. Why won't it put 958AM first?

Thanks in advance

"Frank Kabel" wrote:

Hi
the formula in column A probably causes this error. Try the following
before sorting:
- select column A and copy this column
- goto 'Edit - Paste Special' and choose 'Values' to remove the
formulas

Now try sorting again


--
Regards
Frank Kabel
Frankfurt, Germany


pm wrote:
OK, Thanks,
Row 1, has my column headings
Row 2 has invoice number in 2A, date in 2B, client in 2C and job in
2D. I have 350 rows of client information.
The form was created with all of the invoice numbers in order. My
goal was to sort them by client and keep all of the row info with
that entry.
I should tell you that part way through the entry a formula was used
to add the current invoice number +1, I don't know if that makes a
difference.
Anyway, when I select all 4 columns and 350 rows and fo to Data-Sort
by Client, it does sort by client but not all the invoice numbers
remain with that entry. One thing that it does do is put #VALUE! in
2A???
Does the formula mess it up?
Thanks again.


"Frank Kabel" wrote:

Hi
o.k. can you give an example of what exactly happens after the sort.
What changes within one row?

--
Regards
Frank Kabel
Frankfurt, Germany


pm wrote:
Yes I have.

"Frank Kabel" wrote:

Hi
have you selected ALL relevant columns before going to the menu
'Data - sort'?

--
Regards
Frank Kabel
Frankfurt, Germany


pm wrote:
I'm trying to sort my document that has 4 colums. Invoice #,
Date,
Client and Job.
I want to sort by Client and keep all row information intact.
When I select all and then Sort by Client, Excell does sort by
Client but it is not keeping the row info intact. What am I doing
wrong? Thank you.

Howdy,
Excel will sort numeric values in numeric order.
However the data you are working with is text.
Excel sorts text by the character position, sorting everything by
the first character, then moving on to the second character and so on.
The first character in 958AM is greater than 1 or 4 so it comes last.
That also means that 100PM is going come before 600AM.

You can use two helper columns and extract the AM/PM suffix into
one column and the numeric time values into the other. Then sort
on the AM/PM column first and then the numeric column.

Jim Cone
San Francisco, USA

"Howdy"
wrote in message
...
Hope it's OK to ask this he I am trying to sort a column of times 423PM,
958AM, 1034AM, 1245PM, etc.) but the sort puts the column this way - 1034AM,
1245PM, 423PM, 958AM. Why won't it put 958AM first?
Thanks in advance

On Wed, 21 Sep 2005 15:28:17 -0700, "Jim Cone" wrote:

Howdy,
Excel will sort numeric values in numeric order.
However the data you are working with is text.
Excel sorts text by the character position, sorting everything by
the first character, then moving on to the second character and so on.
The first character in 958AM is greater than 1 or 4 so it comes last.
That also means that 100PM is going come before 600AM.

You can use two helper columns and extract the AM/PM suffix into
one column and the numeric time values into the other. Then sort
on the AM/PM column first and then the numeric column.

A problem with your method is that it will give the following:

953 AM
1121 AM
1243 AM
405 PM
1014 PM
1243 PM

And I think the "proper" order for that list of values should be:

12:43 AM
9:53 AM
11:21 AM
12:43 PM
4:05 PM
10:14 PM


--ron

Ron,
Only if you insist on a day having 24 hours. <g
Regards,
Jim Cone

"Ron Rosenfeld"
wrote in message
...
On Wed, 21 Sep 2005 15:28:17 -0700, "Jim Cone"
wrote:
Howdy,
Excel will sort numeric values in numeric order.
However the data you are working with is text.
Excel sorts text by the character position, sorting everything by
the first character, then moving on to the second character and so on.
The first character in 958AM is greater than 1 or 4 so it comes last.
That also means that 100PM is going come before 600AM.
You can use two helper columns and extract the AM/PM suffix into
one column and the numeric time values into the other. Then sort
on the AM/PM column first and then the numeric column.
A problem with your method is that it will give the following:
953 AM
1121 AM
1243 AM
405 PM
1014 PM
1243 PM
And I think the "proper" order for that list of values should be:
12:43 AM
9:53 AM
11:21 AM
12:43 PM
4:05 PM
10:14 PM
--ron

On Wed, 21 Sep 2005 18:04:42 -0700, "Jim Cone" wrote:

Ron,
Only if you insist on a day having 24 hours. <g
Regards,
Jim Cone

Funny thing about that LOL


--ron

On Wed, 21 Sep 2005 14:16:03 -0700, "Howdy"
wrote:

Hope it's OK to ask this he I am trying to sort a column of times 423PM,
958AM, 1034AM, 1245PM, etc.) but the sort puts the column this way - 1034AM,
1245PM, 423PM, 958AM. Why won't it put 958AM first?

Thanks in advance

Because what you think are times are really text strings.

When sorting text 1 comes before 2 comes before ... 9.

If you want Excel to sort them as times, you have to enter them as times (or
convert them to times).

To convert them to times, you can use this formula:

=TIMEVALUE(INT(LEFT(A1,LEN(A1)-2)/100)&":"&
MOD(LEFT(A1,LEN(A1)-2),100)&" "&RIGHT(A1,2))

After doing the conversions:

Edit/Copy
Edit/Paste Special Values

Then they will sort in time order.

If you are entering the times, then to enter 423PM you must enter it as:

4:23 PM


--ron

Thanks to both Jim and Ron. Question for Ron: I have a spreadsheet with
several time columns. If I want to sort manually through the sort feature do
I still use the same formula?

Thanks again in advance

"Ron Rosenfeld" wrote:

On Wed, 21 Sep 2005 14:16:03 -0700, "Howdy"
wrote:

Hope it's OK to ask this he I am trying to sort a column of times 423PM,
958AM, 1034AM, 1245PM, etc.) but the sort puts the column this way - 1034AM,
1245PM, 423PM, 958AM. Why won't it put 958AM first?

Thanks in advance

Because what you think are times are really text strings.

When sorting text 1 comes before 2 comes before ... 9.

If you want Excel to sort them as times, you have to enter them as times (or
convert them to times).

To convert them to times, you can use this formula:

=TIMEVALUE(INT(LEFT(A1,LEN(A1)-2)/100)&":"&
MOD(LEFT(A1,LEN(A1)-2),100)&" "&RIGHT(A1,2))

After doing the conversions:

Edit/Copy
Edit/Paste Special Values

Then they will sort in time order.

If you are entering the times, then to enter 423PM you must enter it as:

4:23 PM


--ron

On Wed, 21 Sep 2005 16:16:02 -0700, "Howdy"
wrote:

Thanks to both Jim and Ron. Question for Ron: I have a spreadsheet with
several time columns. If I want to sort manually through the sort feature do
I still use the same formula?

Thanks again in advance

I don't understand what you mean by "sort manually"?

But I would suggest that if you want Excel to sort your values in TIME order,
that you first change your text values to Times. The formula (and process of
copy/paste special - values) that I outlined is one way of doing conversion.

If you have some other method of doing the conversion, then you could use your
method.


--ron

Frank Kabel Wrote:
Hi
the formula in column A probably causes this error. Try the following
before sorting:
- select column A and copy this column
- goto 'Edit - Paste Special' and choose 'Values' to remove the
formulas

Now try sorting again


--
Regards
Frank Kabel
Frankfurt, Germany


True that will work, but it will also totally destroy whatever that
formula was meant to do, and judging by this gentlemans Excel novice
status, I'm not 100% sure that you should encourage him to do that
unless he realises the consequences.


--
CFD


------------------------------------------------------------------------
CFD's Profile: http://www.excelforum.com/member.php...o&userid=27306
View this thread: http://www.excelforum.com/showthread...hreadid=237341