List Sorts Numbers By First Digit, Rather Than The Whole Number.

[mathew345]

Hello,

I am inputing some football statistics into an excel sheet, and I am
having some problems with sorting. I have my data in a list, with
filters at top. I am using the filters to sort the data, and all was
going well until I saved the workbook. Now, when I use the filters to
sort, the values are being sorted by the first digit only.

Example: the following numbers 1, 2, 3, 4, 5, 10, 20, 30, 40, 50 would
sort like this

1
10
2
20
3
30
4
40
5
50

I have all the cells formated as numbers, I have tried formatting them
in different ways but to no avail. Any suggestions?

 

[Bruce Sinclair]

Hello,

I am inputing some football statistics into an excel sheet, and I am
having some problems with sorting. I have my data in a list, with
filters at top. I am using the filters to sort the data, and all was
going well until I saved the workbook. Now, when I use the filters to
sort, the values are being sorted by the first digit only.

Example: the following numbers 1, 2, 3, 4, 5, 10, 20, 30, 40, 50 would
sort like this

1
10
2
20
3
30
4
40
5
50

I have all the cells formated as numbers, I have tried formatting them
in different ways but to no avail. Any suggestions?

Sorting and filters are different things. Are you saying that if you turn
the autofilter on, you see 1, 10, 2, 20 ... etc ?
I just tried numbers and text and the autofilter order in bothe cases was
smallest to largest.
This suggests to me that something else is going on. Did you type in the
numbers or import them from a file ? As you suggest, that sort order implies
text rather than numbers if you are talking about a sorted list rather than
autofilter. Suggest that there might be some non printing characters in your
data. The other option is possibly a 'custom list' that specifies that sort
order. Check those out.

HTH

 

[Guest]

Data entered as text sorts exactly as you have described.

Enter 1 in an unused cell and copy it. Then paste/special/mutiply into the
cells you are trying to sort.


Then repeat the sort.

 

[Guest]

Hi

Copy all the columns to a different location in the Excel sheet. This
is basically to run an advance query which will fetch all the records in the
excel sheet even if you have misspled while typing.

Assuming that you have copied all the columns to a single spread sheet, you
know the column heading now, save the excel sheet.

Now I wiil be using the Advance filtering techniques so that all the column
information are retrieved even if the typed cells are misspelled, which
normally is not the case when you do a simple filtering technique. The
filtered values may not be correct.

To retieve the values;

1. Select all records and the column headings and define a name for the
range List. This is the List range name.
2. Select all column headings and paste in a seperate row anywhere in the
spreadsheet.
2. Select the pasted column heading along with an three empty row and define
a
name. This is the criteria range name.

Note: You can specify more criteira in addition rows.

3. Define a criteria on the second row for all columns, if necessary. For
example S* will display all details specific to "S'.
4. Click Data-> Filter-> Advanced Filter
5. Type the List name
6. Type the Criteria Name
7. Click copy to another location option - Mandatory
8. Click Copy to
9. Click the cell below the criteria range

All data will be displayed specific to the query you requested. This is very
usefull if the spreadsheet data are mistyped or mis-spelled. Now you have
done a database funtion on your excle spread sheet.

Challa Prabhu