Spreadsheet Assignment #3: Sorting Data
[Back to Group 1 Assignments] [Back to Chem3020 home]
One of the powerful aspects of using spreadsheets is the ability to rearrange or sort data. This is especially helpful when looking for maximum or minimum values in a group of data or to arrange data with respect to sample ID, customer, or the type of analysis (to name just a few.) Enter the following set of data, then sort the entire group of data and print individual reports after sorting according to:
1. SAMPLE ID (ascending),
2. DATE TESTED (descending),
3. CUSTOMER (ascending),
4. TYPE (descending), and
5. ANALYTE (ascending).
(Note: You may download a text file of this data to your own computer by clicking here: SORTDATA)
|
# |
SAMPLE ID |
CUSTOMER |
DATE
TESTED |
TEST TYPE |
ANALYTE |
|
1 |
K12-A |
Sunshine,
Inc |
03/14 |
AA |
Potassium |
|
2 |
K12-C |
Polymers-4-U |
03/18 |
Titration |
Chloride |
|
3 |
K12-B |
Sweets 'n
Stuff |
05/02 |
UV/VIS |
Phosphorus |
|
4 |
K12-F |
Sunshine,
Inc |
07/31 |
AA |
Gold |
|
5 |
K12-E |
Xactor Drugs |
04/19 |
AA |
Silver |
|
6 |
F22-1A |
Polymers-4-U |
05/03 |
AA |
Silver |
|
7 |
F22-2A |
Polymers-4-U |
09/09 |
AA |
Potassium |
|
8 |
F22-3A |
All-U-Can-B
Chem. |
05/30 |
AA |
Sodium |
|
9 |
F22-4A |
Xactor Drugs |
02/06 |
AA |
Silver |
|
10 |
Z-21 |
Sweets 'n
Stuff |
02/07 |
AA |
Gold |
|
11 |
Z-24 |
Sunshine,
Inc |
03/01 |
Titration |
Chloride |
|
12 |
Z-20 |
WhodunIt Crime
Lab |
04/05 |
Titration |
Fluoride |
|
13 |
Z-36 |
All-U-Can-B
Chem. |
12/06 |
Titration |
Iodine |
|
14 |
Z-78 |
WhodunIt Crime
Lab |
11/07 |
Gravimetric |
Chloride |
|
15 |
Z-01 |
Polymers-4-U |
12/19 |
Titration |
Iodine |
|
16 |
Z-44 |
Xactor Drugs |
10/31 |
AA |
Silver |
|
17 |
Al-45-6 |
Sunshine,
Inc |
06/28 |
AA |
Chloride |
|
18 |
Al-45-3 |
Sunshine,
Inc |
05/22 |
AA |
Silver |
|
19 |
Al-45-1 |
Everything,
Inc. |
10/02 |
AA |
Potassium |
|
20 |
Al-45-9 |
All-U-Can-B
Chem. |
10/22 |
AA |
Sodium |
|
21 |
Al-46-1 |
Polymers-4-U |
11/04 |
Titration |
Silver |
|
22 |
Al-46-2 |
WhodunIt Crime
Lab |
03/07 |
Gravimetric |
Chloride |
|
23 |
Al-46-8 |
Xactor Drugs |
07/04 |
Gravimetric |
Chloride |
|
24 |
Al-46-3 |
Sweets 'n
Stuff |
08/02 |
Titration |
Fluoride |
|
25 |
L-19-G |
Everything,
Inc. |
09/04 |
AA |
Silver |
|
26 |
L-19-H |
WhodunIt Crime
Lab |
10/29 |
AA |
Gold |
|
27 |
L-24-A |
Xactor Drugs |
03/18 |
AA |
Potassium |
|
28 |
M-83-J |
Sweets 'n
Stuff |
05/02 |
Titration |
Fluoride |
|
29 |
M-83-K |
Everything,
Inc. |
03/01 |
UV/VIS |
Phosphorus |
|
30 |
M-83-U |
Sweets 'n
Stuff |
10/31 |
AA |
Sodium |
|
31 |
M-82-B |
Sunshine,
Inc |
10/29 |
AA |
Sodium |
|
32 |
M-82-P |
Xactor Drugs |
10/29 |
AA |
Sodium |
|
Excel Programming Tips: |
|
-To sort data, highlight the region of the spreadsheet to be sorted and select from the menu bar: Data > Sort. Then, complete the menu box and click OK. |
|
- When sorting columns such as this one make sure that you select the menu box option, “My list has… ● header row.” If “● no header row” is checked and you have highlighted the headers as part of the sort, the headers will be sorted with the rest of the data. |
|
- Underlined letters in menus indicate that that letter may be used to select that menu item from the keyboard instead of clicking with a mouse. For example, holding [Alt] and pressing the keys T O G will select Tools > Options > Gridlines that toggles gridlines on and off. |