Results 1 to 13 of 13
Thread: Columns  Sorting (Excel 2003)

20031125, 21:55 #1
 Join Date
 May 2003
 Location
 Nottinghamshire, England
 Posts
 211
 Thanks
 0
 Thanked 0 Times in 0 Posts
Columns  Sorting (Excel 2003)
Hello everyone:
Can anyone advise me on how to complete the following:
I have 13 columns I only want to sort 3 columns for example
Column C (Company  Corporation) Column A (Contact Name) Column M (Renotification)
I have tried everything, I have click on the entire range of my data; click data and used the sort by; then by; then by. Then only column that will sort is the main and most important column to me which is the Column C.
I have even gone into the Tools/Options and Custom List, I know that I am doing something wrong, but I don't know what.

20031125, 22:09 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Columns  Sorting (Excel 2003)
If the range to sort forms a contiguous block, i.e. there are no entirely blank rows or columns within the block, and it is bounded by at least one empty row and column, you can select an arbitrary nonblank cell in the range. Select Data  Sort, and enter the three columns you want to sort on, from most important to least important, i.e. C, A, M, if I understand you correctly. The entire range should be sorted on these three keys when you click OK.
If the range to sort is not contiguous, you must select it before sorting.
IF it still doesn't work, check the kind of data you have in columns A and M. If you like, you can post the workbook.

20031125, 22:17 #3
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Columns  Sorting (Excel 2003)
Do you want to sort Cols A, C, & M as a group and leave the others unsorted? If so I suggest you select Col C and hold down Shift whilst dragging it left to between Cols A & B, this will put it in new Col B and move existing Col B right. Repeat for Col M and drag it to Col C position. You will now have the columns you want to sort in Cols A, B, & C. Select the data in these columns and you should be able to sort it. XL will say you have data next to the selected data, do you want to Expand the selection or Continue with the current selection, choose the latter.

20031127, 00:04 #4
 Join Date
 May 2003
 Location
 Nottinghamshire, England
 Posts
 211
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Columns  Sorting (Excel 2003)
Hello Hans:
If I understand you correctly, are you are saying that If I have a blank cell in the column(s) that I am trying to sort, then the sort option will not be available for that particular spreadsheet that I am working on.
If this is the case should I fill the blank cell with 9999?
Thank
dillon65

20031127, 00:11 #5
 Join Date
 May 2003
 Location
 Nottinghamshire, England
 Posts
 211
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Columns  Sorting (Excel 2003)
Hello Mr. Evans:
Thank you for your support and input. I do want to only select C,A,M at the same time I must select the entire data which starts from A53:M53. Then I have gone to Data/Sort and then I choose the C,A,M; I should let you know that I have conditional formatting in the L column this formatting give me the end result in the M column.
Would you be inclined to say that conditional formatting could be the culprit?
Thanks
dillon65

20031127, 00:23 #6
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Columns  Sorting (Excel 2003)
Data for sorting must be contiguous, XL will tell you this if your select more than one range and then try and sort. Thus if you want to sort only 3 columns you will have to move them alongside one another as I suggested.
To find out if conditional formatting is causing trouble, I suggest you make a values only copy of your data, remove any coditional formatting, and try sorting that.

20031127, 00:28 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Columns  Sorting (Excel 2003)
No, I didn't mean that. Look at this "spreadsheet":
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><tr><td align=center>1</td><td>Year</td><td>State</td><td align=right>

20031127, 00:32 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Columns  Sorting (Excel 2003)
Could you explain again what you are trying to sort?
My understanding is"
you select A1:M53 and choose datasort
Sort by: COlumn C
Then By: Column A
Then by Column M
<OK>
If you do this you will sort by col C. If any entries in column C are identical it will sort those by col A, if the entries in col c are identical and the entries are also identical in col A, then it will sort by col M
All the data in the range A1:M53 will be sorted so that items in one row will remain together.
If you want to sort cols C, A, and M independently of each other and the other rows. You can highlight col C and choose datasort and the tell excel not to expand the selection. You can choose A and do the same, then M and do the same. After this, cols B and DL will not be changed. The items Cols C, A, and M, will no longer have any relationship to each other and neither will they to the other cols.
If you want to sort cols C, A, and M as a group and not sort any of the other cols, you either have to write a macro, since excel will not sort noncontiguous ranges. If you want to do this the easiest thing to do would be to move the columns so they were all together, then select the 3 and sort them. You could always move them back when you are done.
About blanks in your data, in selecting excel might have a hard time "figuring out" what range to sort if it has large "blank rows or columns". It usually can handle individual blanks okay. When blanks are sorted, they always go to the end in a sorted column no matter how you sort.
Steve

20031127, 01:29 #9
 Join Date
 May 2003
 Location
 Nottinghamshire, England
 Posts
 211
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Columns  Sorting (Excel 2003)
Hello Steve and Hans:
Hans I did understand your diagram, and yes I have been doing exactly what you have instructed me to do but only the column C gets sorted.
This would be because I have chosen Data/Sort C,A,M  A,M don't change.
Steve, I made a mistake A4:M53 I have selected the entire data range before I use the Date/Sort sequence, I will go over Hans's post as well as yours to see if I have missed a step, but the procedure is so basic I just can't see how I could of missed anything.
Thanks
dillon65

20031127, 02:01 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Columns  Sorting (Excel 2003)
If you select the entire range, and sort on columns C, A and M, it is impossible that columns A and M remain completely unchanged except in the unlikely situation that they are already in the correct order. This makes me wonder (like Steve) what you really mean by sorting.
Here is another example:
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Year</td><td>State</td><td>Employee</td><td align=center>2</td><td align=right>1999</td><td>WA</td><td>John</td><td align=center>3</td><td align=right>2000</td><td>OR</td><td>Mary</td><td align=center>4</td><td align=right>2000</td><td>WA</td><td>Eve</td><td align=center>5</td><td align=right>1999</td><td>OR</td><td>Harry</td><td align=center>6</td><td align=right>2000</td><td>CA</td><td>Anne</td><td align=center>7</td><td align=right>1999</td><td>OR</td><td>Bill</td><td align=center>8</td><td align=right>2000</td><td>WA</td><td>Rick</td><td align=center>9</td><td align=right>1999</td><td>OR</td><td>Greg</td></table>
When this table is sorted on columns A, B and C (in that order), the result is
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Year</td><td>State</td><td>Employee</td><td align=center>2</td><td align=right>1999</td><td>OR</td><td>Bill</td><td align=center>3</td><td align=right>1999</td><td>OR</td><td>Greg</td><td align=center>4</td><td align=right>1999</td><td>OR</td><td>Harry</td><td align=center>5</td><td align=right>1999</td><td>WA</td><td>John</td><td align=center>6</td><td align=right>2000</td><td>CA</td><td>Anne</td><td align=center>7</td><td align=right>2000</td><td>OR</td><td>Mary</td><td align=center>8</td><td align=right>2000</td><td>WA</td><td>Eve</td><td align=center>9</td><td align=right>2000</td><td>WA</td><td>Rick</td></table>
As you can see, all three columns have been changed. The only column that ends up strictly ordered is column A, because that is the first column I sorted on. Column B is not sorted in its entirety  cells B2:B5 are sorted because they belong with Year = 1999, and B6:B9 are sorted because they belong with Year = 2000. Similarly, column C is not sorted in its entirety, but for instance cells C1:C3 are sorted, because they belong with Year = 1999 and State = OR.

20031127, 02:52 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Columns  Sorting (Excel 2003)
It is impossible to say what is happening from seeing one column only. Since sorting is done on a range as a whole, we would need to see the data in context, not a single column.
By the way, if what you posted is in one column, the names will be sorted (insofar as they are sorted) by first name.

20031127, 10:13 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Columns  Sorting (Excel 2003)
Are the other columns, perhaps formulas instead of text? If they are formulas which reference some other cell or other sheet, the cells would be sorted, sorting the formulas, but the underlying "output" from the formulas might not change, because if you have "relative references" in the formulas the cell formulas will remain identical after sorting.
Steve

20031127, 15:39 #13
 Join Date
 May 2003
 Location
 Nottinghamshire, England
 Posts
 211
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Columns  Sorting (Excel 2003)
I have pasted column A when I select the entire range of my data A4:M54; then I click data then click sort, the prompt comes up and says "Sort by, Then by, Then by". I choose C, A, M, is this not considered as sorting? I have pasted column A, but it doesn't sort  Column C is the only one that gets sorted A,M remain unchanged.
Hans and Steve, thank you both for your support I will spend tomorrow brewing over your post, and see what I can come up with.
I will repost my solution and also my mistake(s)
Again, Thank you both
dillon65