sorting

[Lee Bowman]

This is pretty basic, I guess, but I'm looking for the proper way to
sort by any column and have the data stay related.

If I create a range of A:1-D:4 like this:

NOW IS THE TIME
FOR ALL GOOD MEN
TO COME TO THE
AID OF THEIR PARTY

and sort on the first column, it becomes:

AID IS THE TIME
FOR ALL GOOD MEN
NOW COME TO THE
TO OF THEIR PARTY

and of course, I just ruined my data! I want each column enrty of a
given line to stay related (as though each line was a record in a
database, and each column was a field.

Of course, by selecting the whole range and sorting, it will do it
correctly. That's fine, if you sort by the first column, but ...

Question: Is there a way to sort by _any_ column and have the data
in each row stay related? Like this, sorting the second column:

FOR ALL GOOD MEN
TO COME TO THE
NOW IS THE TIME
AID OF THEIR PARTY

I know that a database in table form will behave this way, but I'd
like to do it in Excell (quicker and easier)

Thanks in advance!

Lee Bowman

 

[Dave Peterson]

Make sure you select the whole range (all the columns and all the rows) before
you do Data|Sort.

And try to stay away from those sort icons on the toolbar unless you're positive
xl will guess correctly.

 

[Lee Bowman]

Make sure you select the whole range (all the columns and all the rows) before
you do Data|Sort.

And try to stay away from those sort icons on the toolbar unless you're positive
xl will guess correctly.

I was sorting with the toolbar button, and it defaults to sorting the
first column only. I was unaware of the DATA : SORT which lets you
pick the column (and up to two more).

The toolbar button is not only useless (except for first column
sorts), but dangerous, since it will let you sort any selected column
by itself. I can think of no time you'd sort any column by itself.

An imrovement to the toolbar icon function would be:

1/ Offering you a choice of which column to sort (like the DATA :
SORT menu option.
2/ Sorting the selected column but preserving the data by moving the
other columnar data to stay in the same rows.
3/ At least warn you that you that if you sort one column by itself
you will disassociate your data.

I have Excel 2000. I wonder if other releases treat sorting
differently.

 

[Dave Peterson]

xl2002 added a warning to help:

Microsoft Excel found data next to your selection.
Since you have not selected this data, it will not be sorted.

What do you want to do?
o Expand the Selection
0 Continue with the current selection

(two buttons: Sort and Cancel)

I bet lots of people complained to get this.

 

[Lee Bowman]

What do you want to do?

o Expand the Selection
0 Continue with the current selection

(two buttons: Sort and Cancel)

I bet lots of people complained to get this.

You would think so. Like the old, "Are you sure?" warning ...

 

[Guest]

DOES ANYONE KNOW HOW I CANCEL THIS "COVETED" SORT WARNING?

----- Lee Bowman wrote: -----

What do you want to do?
o Expand the Selection
0 Continue with the current selection

You would think so. Like the old, "Are you sure?" warning ...

 

[Dave Peterson]

Include at least two columns in your range to be sorted.

(Insert a dummy column if you need to and delete it when you're done.)

I'm still glad they added this warning.

 

[Dave Peterson]

Just guesses...

Do you have empty columns between your ranges--the area that sorts ok and the
area that doesn't?

For my part, I hardly ever let excel guess at the range of cells that I want
sorted. (Unless it's a single column worksheet, I'll select my range first.)

I'm just too afraid that I won't notice the foul-up until it's too late.

 

[Guest]

I have over 500 rows of data (customer records) and each row has some blank cells, not all the same. I guess what I don't understand is how it could all of a sudden "just not work" after 6 months of running perfectly. I've been sorting and resorting the same worksheet dozens of times each day, never with a problem (other employees also use the lists I generate and they are very good about pointing out minor errors, so I know they would certainly have pointed out any major glitches with the data). I cannot locate any options in sorting preferences that could possibly have been reset somehow, only the new warning window, which I have consistently answered the same. My approach has always been to select the column I want to sort by, then select the sort button in the toolbar, then tell it to proceed with the sort by moving all of the associated data as well...I'm still scratching my head and will continue to search the Microsoft knowledge base. Thanks for your help!

 

[Dave Peterson]

Good luck with your search. I'll continue to select the complete range--I feel
much safer when I do this.

But to make your life easier, you may be able to use a defined name to make the
selection of the range easier.

Debra Dalgleish has some nice instructions at:
http://www.contextures.com/xlNames01.html#Dynamic

So you could just hit F5, type in that name and your complete range would be
selected.

In fact, you could sort the range in code and just run the macro when you need
to sort.

(I've seen a few posts that suggest that the users remove those a-z and z-a
icons from their toolbars. They can screw up data so that's it unrecoverable.)