If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Sorting by Group of Cells
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 |
#3
|
|||
|
|||
Sorting by Group of Cells
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. |
#4
|
|||
|
|||
Sorting by Group of Cells
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 |
#5
|
|||
|
|||
Sorting by Group of Cells
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-" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting Area with Merged Cells | prizm1 | New Users | 3 | September 10th, 2005 11:49 PM |
Microsoft Excel - Grouping and Sorting Cells | Michael Blogg | Worksheet Functions | 1 | May 26th, 2005 09:27 PM |
ambiguous outer joins | renwick | Running & Setting Up Queries | 3 | February 22nd, 2005 01:29 PM |
Help Needed for Groups Please | Paul Black | General Discussion | 15 | June 21st, 2004 02:54 AM |
How to Manipulate the group and sorting in a report? | Jorge Novoa | Using Forms | 2 | June 9th, 2004 01:12 AM |