excel - sort rows

Question

I have numbered rows, but can't put them in numerical order. I've tried SORT function, but that doesn't seem to work either. Any suggestions?

Answer 1

Difficult to be sure without an example but you could have the numbers entered as text. If so, when sorting select the option to sort as numbers. See the Screen Shot

Alternatively, convert the text numbers to real numbers as follows.

1. Select the column
2. Select Text to columns (Data ribbon)
3. In the dialog, select Fixed width -> Next
4. If any vetical lines through the data column on the dialog then double click them to remove
5. Click Next
6. Click Finish
7. Try sorting again

If the above is not the problem then need more information. Try using Snipping tool to create a screen shot. Try to include the column header id's and row numbers in the Screent shot and thenupload with your post. Use the Insert Image icon in the post editor and follow the prompts.

Answer 2

When you select Sort, the dialog has a check box to indicate if the selected data contains a column header and if it is checked then it thinks the first cell of the selection is a column header and does not get included in the sort. If the selection does not contain a column header then the box needs to be unchecked.

Refer to the Screen shot. If the box is checked then 12 would remain in cell A1 but if Unchecked then the 12 is included in the sort

Answer 3

OssieMac,

Still having problems in sorting. I tried "Extend the Selection" using col 1 w/ #3 at the top. I was able to order all associated cols numerically, except #3 which remained at the top. How can you get #3 row to fall in line? Am I missing other steps.

The attached PDF shows col 1 #s still mixed, but you can disregard everything below #3. As mentioned, all #s below #3 sort numerically.

JKSJIM

Answer 4

When I used the DATA tab to select Sort, I didn't get the options on your attachment, but got "Expand the selection" & "Continue with the current selection". I chose the 2nd option. Lesson learned: create a separate col for formatted numbers, dates, etc., then sort.

If I am not sure if I am interpreting your comments correctly and/or you are interpreting the options correctly.

"Continue with the current selection" means it only sorts the specific selected column and all other data in adjoining rows remains as is (Unsorted).

"Expand the selection" means to include the adjacent columns so that all data in a particular row remains together on the same row after the sort of the selected column.

Personally, if I want to sort all of the data to match a specific column (or columns) then I select all columns and sort and then a dialog pops up as to which column to sort on and can add additional columns to that so the additional columns sort based on the previous column.

eg. A list of names that includes first and last names and the first column to sort might be family name and second column is given name so that family names are together and the given names are alphabetical within each family name.

Answer 5

OssieMac,

Not sure if I replied to your answer, but in case I didn't, your answer set me on a very similar path. When I first numbered each row, I wrote # & text in the same col. Obviously, that was mistake. Next I created a separate col formatted as numbers. When right clicked, I could sort smallest to largest or largest to smallest.

When I used the DATA tab to select Sort, I didn't get the options on your attachment, but got "Expand the selection" & "Continue with the current selection". I chose the 2nd option. Lesson learned: create a separate col for formatted numbers, dates, etc., then sort.

Thanks, JKSJIM