Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 non-blank 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.

  3. #3
    3 Star Lounger
    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.

  4. #4
    3 Star Lounger
    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

  5. #5
    3 Star Lounger
    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

  6. #6
    3 Star Lounger
    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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 data-sort
    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 data-sort 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 D-L 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 non-contiguous 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

  9. #9
    3 Star Lounger
    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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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

  13. #13
    3 Star Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •