sorting

[autumn]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: intel

I think my question is very basic, but try as I may I just cannot figure this out! help appreciated.

I have 8 columns of information, 3 of which are the most relevant. How do I sort the whole body of data so that the main column is sorted, but so is each row that goes with it.

thanks!

 

[Bob Greenblatt]

I'm having trouble visualizing what your data looks like? When you select
the whole range and sort on the column, what happens that is incorrect?

 

[autumn Preble]

Hi Bob, thank you for your reply.

I have created a ledger for financial info. There are 8 columns of info.
The first is date, the next two are text data, the fourth has numbers, etc.
There are pages and pages of this (this was my personal accounting for 2007).

What I'm wanting to do is take the 3rd column which is 'categories' (eg. medical, household, personal, etc) and alphabetize it so I can group, for example, all the medical. When I select that column and sort it, it only sorts that particular column.

I would like the entire spreadsheet sorted following the alphabetizing of that column, in other words, that all the rows are rearranged according to the alphabetization of that one category column.

does this make any sense? what do you mean by "when you select the whole range"?

thanks,
Autumn

 

[CyberTaz]

Hi Autumn - See the in line comments;

Hi Bob, thank you for your reply.

I have created a ledger for financial info. There are 8 columns of info.
The first is date, the next two are text data, the fourth has numbers,
etc.
There are pages and pages of this (this was my personal accounting for
2007).

What I'm wanting to do is take the 3rd column which is 'categories' (eg.
medical, household, personal, etc) and alphabetize it so I can group, for
example, all the medical. When I select that column and sort it, it only
sorts that particular column.

That's your problem - don't select the *column* of cells. If you do Excel
thinks you just want to sort its content. Simply put the cell selector
anywhere in the column or select just the caption, then Sort A-Z.

I would like the entire spreadsheet sorted following the alphabetizing of
that column, in other words, that all the rows are rearranged according to
the alphabetization of that one category column.

does this make any sense? what do you mean by "when you select the whole
range"?

It makes perfect sense He means the whole "range" of cells occupied by
your data, which is another option but not necessary for what you want to
do.

BTW, if you want to sort by more than just the one column (such as within
category by date or amount) choose the Sort command in the Data menu. You
can sort by as many as 3 fields. Once the records are sorted you might also
want to check out the Data> Subtotals feature.

 

[autumn Preble]

when I place my cursor anywhere in Column C, then Sort A-Z, I get the message "this operation requires the merged cells to be identically sized".
If I sort from the data menu, I get the same message. I do not know that I had any merged cells and not sure how to identically size them . . .

 

[CyberTaz]

Well, there must be some merged cells in your list somewhere You should
be able to see where they are because they will typically be either wider or
narrower than the rest in the 'same' column. It's also possible that cells
may have been merged vertically, however, so the height would be the
giveaway, rather than the width. And it's also possible that multiple cells
is more than one column & row have been merged. Any of these will cause the
message to appear when you attempt to sort. [To Quote one JE McGimpsey,
"Merged Cells are the spawn of the Devil!"]

If you truly can't identify where the merging has been done the only
suggestions I can offer are;

Select the range of data & copy it, then go to another location (preferably
a new blank sheet) & use Edit> Paste Special - Formula & Number Formats.
Afterward you'll most likely need to remove some empty columns and/or rows
in order to clean things up and most likely need to do some reformatting.

Alternatively:
Select the entire range of your data then go to Format> Cells - Alignment.
You'll see a checkbox at the bottom which will have a - in it rather than a
check (indicating that some selected cells are merged, some are not). Click
the checkbox once or twice to clear the box, then OK. NOTE: There is no
doubt that this will cause disarray in your list. You'll have to do some
work to get things straightened up - how much depends on how many & what
type of merges were done.

What your goal is: A valid list of consecutive columns & rows with captions
in the first row and containing no merged cells. Once you accomplish that
the Sorting should work like a charm

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Carl Witthoft]

Well, there must be some merged cells in your list somewhere You should
be able to see where they are because they will typically be either wider or
narrower than the rest in the 'same' column. It's also possible that cells
may have been merged vertically, however, so the height would be the
giveaway, rather than the width. And it's also possible that multiple cells
is more than one column & row have been merged. Any of these will cause the
message to appear when you attempt to sort. [To Quote one JE McGimpsey,
"Merged Cells are the spawn of the Devil!"]

Let me second that wholeheartedly!

There is absolutely no reason ever to merge cells. It's cosmetic only,
and raises holy hell with sorts, insertions, and a gazillion other
functions.

If you must enclose text in a larger 'box' just split it into a few
cells (vertically) and put a nice border around the cell group.

Which brings up another point: many people never find out that
displaying gridlines on the screen has NOTHING to do with printing
gridlines. I always recommend displaying gridlines on the screen so
you know what you're doing , and if you really don't like them in
the final product, apply borders where you want them, and under
PageSetup or Print.... select not to print gridlines.

 

[autum]

I am appreciating your responses and learning . . . and still am not yet solving my problem. Is there a way to UnMerge All Cells, as I SEE no merged cells, nor did I consciously use that function. Is there any way to un-do without going through ten pages cell by cell?

meanwhile, in all my mucking about with this I've gotten some of the columns' info out of line with the original data, is there any way to revert to original?

thanks!
Autumn

 

[autum]

Bob, I just noticed your email, which I haven't tried yet with ideas about how to un-merge. I'll give that a try and get back to you.

 

[autumn Preble]

YES!!! HOORAY!!!

I began picking through my data with a fine toothed comb, and in fact, there were merged cells . . . not sure how they got there. One possible way is when I type text in a cell and spill over into the next column . . . there weren't many of those, but there were other merged cells that were a mystery. Well, now I know to look on the Formatting Palette and see the Merge box, making sure it is unchecked.

I've learned!

thanks to you and to the others who wrote.
Autumn

 

[CyberTaz]

Congratulations!!!

What may have happened is that you mistakenly used the Merge & Center button
rather than applying Center alignment... It's normal behavior for *text* to
overflow the edge of a cell if the adjacent cells have no content. That in
itself doesn't cause any merging - the Merge Cells command must be
explicitly issued, and that can't effectively be done unless you have 2 or
more cells selected at the time.

As you say, it's a learning experience

Good Luck |:>)
Bob Jones
[MVP] Office:Mac