Columns – Sorting (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

No, I didn’t mean that. Look at this “spreadsheet”:

	A	B	C	D	E	F
1	Year	State

Reply | Quote

No, I didn’t mean that. Look at this “spreadsheet”:

	A	B	C	D	E	F
1	Year	State

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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:

	A	B	C
1	Year	State	Employee
2	1999	WA	John
3	2000	OR	Mary
4	2000	WA	Eve
5	1999	OR	Harry
6	2000	CA	Anne
7	1999	OR	Bill
8	2000	WA	Rick
9	1999	OR	Greg

When this table is sorted on columns A, B and C (in that order), the result is

	A	B	C
1	Year	State	Employee
2	1999	OR	Bill
3	1999	OR	Greg
4	1999	OR	Harry
5	1999	WA	John
6	2000	CA	Anne
7	2000	OR	Mary
8	2000	WA	Eve
9	2000	WA	Rick

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.

Reply | Quote

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:

	A	B	C
1	Year	State	Employee
2	1999	WA	John
3	2000	OR	Mary
4	2000	WA	Eve
5	1999	OR	Harry
6	2000	CA	Anne
7	1999	OR	Bill
8	2000	WA	Rick
9	1999	OR	Greg

When this table is sorted on columns A, B and C (in that order), the result is

	A	B	C
1	Year	State	Employee
2	1999	OR	Bill
3	1999	OR	Greg
4	1999	OR	Harry
5	1999	WA	John
6	2000	CA	Anne
7	2000	OR	Mary
8	2000	WA	Eve
9	2000	WA	Rick

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote