Sorting by Group of Cells

I doubt this can be done, but it sure would be handy.

Column is grouped by conceptual blocks, (note, there is not second column of
unique data) eg:

Cheese
Cheddar
Blue
Swiss
Bread
Italian
Bran
French

I want to sort by alpha so that I get (evenutally):

Bread
Bran
French ...
Cheese
Blue
Cheddar ...

Aside from setting up the table in two columns (Bread/Cheese; Types) and
making sure Bread/Cheese is in each column-cell that correspondes to the
types and THEN sorting - is there another way to do this? Grouping?
Protection?

thx

It sure seems like the easiest approach would be to put the data in separate
columns.

Depending on how the data was entered, you could extract the values with a
couple of formulas:

If you actually indented those type entries with extra spaces, you could do
something like this:

(Assumes the data is in A1:Axx)

Put this in B1:
=a1
put this in B2:
=IF(LEFT(A2,1)=" ",B1,A2)
select b2 and drag down to Bxx.

Put this in C1:
=""
Put this in C2:
=IF(LEFT(A2,1)=" ",TRIM(A2),"")
Select c2 and drag down to Cxx.

Then select columns B:C
edit|copy
edit|paste special|values

Select columns A:C
sort the selection with a primary key of column B and a secondary key of column
C.

Delete columns B:C when you're done.
=======
If you didn't use extra spaces to indent those types, then how do you know
what's a category and what's a type?




-random- wrote:
>
> I doubt this can be done, but it sure would be handy.
>
> Column is grouped by conceptual blocks, (note, there is not second column of
> unique data) eg:
>
> Cheese
> Cheddar
> Blue
> Swiss
> Bread
> Italian
> Bran
> French
>
> I want to sort by alpha so that I get (evenutally):
>
> Bread
> Bran
> French ...
> Cheese
> Blue
> Cheddar ...
>
> Aside from setting up the table in two columns (Bread/Cheese; Types) and
> making sure Bread/Cheese is in each column-cell that correspondes to the
> types and THEN sorting - is there another way to do this? Grouping?
> Protection?
>
> thx

--

Dave Peterson

Nothing comes to mind, short of doing the dirty work of assigning a
category and subcategory to each entry. If I may editorialize for a
moment, to my thinking that exercise would come in handy in the future
for summarizing or totalling or what have you. You could hide the
category and subcategory columns if they don't fit into your aesthetics.

I have a solution for this using the CODE function but it is a multistep
process and it takes up a number of columns of formulas to get your sort.
It wont fit very well in this reply window, but I could email you the
spreadsheet if you post an email address.

The CODE function converts letters to a number(I believe its ASCII codes).
In any case once you convert the letters to numbers you can sort them easier
- but its still not simple - at least my method was not. I'm happy to send
my solution.

Dave

"-random-" wrote:

> I doubt this can be done, but it sure would be handy.
>
> Column is grouped by conceptual blocks, (note, there is not second column of
> unique data) eg:
>
> Cheese
> Cheddar
> Blue
> Swiss
> Bread
> Italian
> Bran
> French
>
> I want to sort by alpha so that I get (evenutally):
>
> Bread
> Bran
> French ...
> Cheese
> Blue
> Cheddar ...
>
> Aside from setting up the table in two columns (Bread/Cheese; Types) and
> making sure Bread/Cheese is in each column-cell that correspondes to the
> types and THEN sorting - is there another way to do this? Grouping?
> Protection?
>
> thx

You will have to do a little work with macros but you should be able to
adapt http://www.mvps.org/dmcritchie/excel/fillempt.htm to your needs

In fact even without macros, but it takes more steps like converting to values.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"-random-" <-random-@discussions.microsoft.com> wrote in message news:37400813-3203-4D6A-B62B-E30E6E92E82B@microsoft.com...
> I doubt this can be done, but it sure would be handy.
>
> Column is grouped by conceptual blocks, (note, there is not second column of
> unique data) eg:
>
> Cheese
> Cheddar
> Blue
> Swiss
> Bread
> Italian
> Bran
> French
>
> I want to sort by alpha so that I get (evenutally):
>
> Bread
> Bran
> French ...
> Cheese
> Blue
> Cheddar ...
>
> Aside from setting up the table in two columns (Bread/Cheese; Types) and
> making sure Bread/Cheese is in each column-cell that correspondes to the
> types and THEN sorting - is there another way to do this? Grouping?
> Protection?
>
> thx