help with the simplest sort

[Dave_D]

very little excel experience here.

I imported an address list and want to sort it by different columns, but
when I choose a cell in the "LastName" column and sort, only that column
sorts, mixing the last names up with the other data. I want all the columns
to sort. This doesn't happen on all worksheets, I make simple ones and
practice and it does what I want. What am I missing?

 

[Otto Moehrbach]

Dave
Excel tries, bless it's heart, to figure out what your sort range is but
it's not always right. This usually causes pain as you saw. What you
should do is select your entire range to sort. Then click on Data - Sort.
If you included the column headers in the selection, then make sure that
"Header row" is checked. Click on OK and it's done!
Excel is usually right, in the selection of the range to sort, if you
have no blank columns and the active cell is within the range. But if you
want to make sure, select the range yourself. HTH Otto

 

[Dave_D]

Thanks, the blank columns were the problem. Quickbooks exports the data to
excel with a blank column between all the data columns, deleted them and the
sort works fine.

Maybe you can answer this one. I have a date formatted column like
01/02/03. Sorting this column does not give me what I expect. It puts
01/28/04 before 01/29/03. Doesn't seem to see the year. ??

 

[Pauline]

Hi Dave, Try Data, Sort. Then sort by the first column, then by the second
column, then the third, etc. Click okay.
Hope this is what you want.

Pauline

 

[Otto Moehrbach]

Dave
The problem you describe is a common one where you are using data that
is imported. What is happening is that Excel sees these entries as text,
not numbers. This doesn't happen with all numbers all the time. Just some
numbers some of the time to give you heartburn.
If the problem numbers were just numbers (instead of dates) you could
simply do some mathematical action to them and this changes them to numbers.
Something like multiply by 1 or add 0 to them. But if you have a date that
is actually text (to Excel) then you have a trickier problem. You will have
to use a function to change the text to a date.
You said the dates looked like 01/24/04. Is that always the format?
That is, mm/dd/yy with two digits for each regardless of the value? IOW, is
it ever 1/28/04?
I'll get you up a function for this in the morning. I have to get my
beauty sleep although I'm losing faith in it. Otto

 

[Dave_D]

You said the dates looked like 01/24/04. Is that always the format?

they are all like this.

 

[Otto Moehrbach]

Dave
This was easier than I thought.
Say your "dates" are in Column A starting in A1.
Insert a blank column B (Select Column B and click on Insert - Columns).
In cell B1 type "=DATEVALUE(A1)" without the quotes.
You will get a 5 digit number in B1. That's OK for now.
Drag cell B1 all the way down column B as far as Column A has data.
You should now have 5 digit numbers all the way down Column B.
Select all the numbers in Column B.
Click on Edit - Copy,
Select cell A1.
Click on Edit - Paste Special - and select Values. Click OK.
You should now have real dates in Column A or a string of 5 digit numbers.
If you still have the 5 digit numbers, select all the numbers in Column A.
Click on Format - Cells - Number tab - Date and select the format you want
your dates to look like.
Delete Column B. (Select Column B and click on Edit - Delete).
Done. HTH Otto

 

[Dave_D]

Thanks.

Dave
This was easier than I thought.
Say your "dates" are in Column A starting in A1.
Insert a blank column B (Select Column B and click on Insert - Columns).
In cell B1 type "=DATEVALUE(A1)" without the quotes.
You will get a 5 digit number in B1. That's OK for now.
Drag cell B1 all the way down column B as far as Column A has data.
You should now have 5 digit numbers all the way down Column B.
Select all the numbers in Column B.
Click on Edit - Copy,
Select cell A1.
Click on Edit - Paste Special - and select Values. Click OK.
You should now have real dates in Column A or a string of 5 digit numbers.
If you still have the 5 digit numbers, select all the numbers in Column A.
Click on Format - Cells - Number tab - Date and select the format you want
your dates to look like.
Delete Column B. (Select Column B and click on Edit - Delete).
Done. HTH Otto
IOW, range