Ms Access And Ms Excel - What To Do When Sorting Does Not Work
Below is a MRR and PLR article in category Computers Technology -> subcategory Web Development.
Troubleshooting Sorting Issues in MS Access and Excel
Sorting data in Microsoft Access and Excel is usually a straightforward task, using toolbar buttons to sort columns in ascending (1 ... 999) or descending (999 ... 1) order. These buttons are clearly marked, but what happens when sorting doesn't work as expected, especially with mixed data types like text and numbers? This guide will help address such issues.
Understanding Basic Sorting
Both Access and Excel offer buttons for sorting:
- Ascending: Displays a blue "A" above a red "Z" with a downward arrow.
- Descending: Shows a red "Z" above a blue "A" with a similar arrow.
To sort, simply place your cursor in the desired column and click the appropriate button. This works well for purely text or numeric data, but mixed content can be problematic.
Troubleshooting Mixed Data Sorting
Let's explore examples to illustrate common sorting issues:
Example 1: Sorting Part Numbers
Consider part numbers like:- W4
- W39
- W212
Example 2: Sorting Addresses
Consider addresses such as:- 4 Main Street
- 39 Main Street
- 212 Main Street
In both cases, computers sort differently than humans expect. A computer might order W212 before W4 because it simplifies sorting to a basic algorithm.
Solving the Part Numbers Issue
If possible, standardize part numbers with consistent character lengths, like:
- W000004
- W000039
- W000212
If standardization isn't feasible, create a new field in Access or a new column in Excel. Label it as "PartNumberSortable." Here's how:
In Access:
Create a query to convert part numbers to a sortable version:```sql
left([PartNumber],1) & format(mid([PartNumber],2),"000000")
```
This query allows classic part numbers to be displayed but sort using the "PartNumberSortable" field, which remains hidden.
In Excel:
Use a formula for the new column:```excel
=LEFT(A1,1) & RIGHT("000000" & MID(A1,2,8),6)
```
Assuming part numbers are in column A, this formula will sort them correctly.
Address Sorting: A Future Discussion
Address sorting is more complex and will be tackled in another article. Meanwhile, for more helpful MS Access tips, visit [Database Lessons](http://www.databaselessons.com).
Note: These examples were tested on MS Office 2000; newer versions might have enhanced sorting capabilities.
You can find the original non-AI version of this article here: Ms Access And Ms Excel - What To Do When Sorting Does Not Work.
You can browse and read all the articles for free. If you want to use them and get PLR and MRR rights, you need to buy the pack. Learn more about this pack of over 100 000 MRR and PLR articles.