| Order History Data - Jan 1 - May 30, 2020 |
|
|
|
|
|
|
|
|
| Data removed as invalid (if possible, fix data and replace) |
|
|
| Step 1: Cleanse data for .. (save this data on the right for later inspection and/or correction) |
|
|
|
|
|
|
|
| (insert data here with errors highlighted) |
|
|
| a. Invalid Invoice # (must start with A followed by 7 digits) |
|
|
|
|
|
|
| 01/12/20 |
B245439 |
10234 |
81 |
01/17/20 |
1/17/2020 |
|
|
| b. Invalid Part # (only the 4 listed in Reference Table are valid) |
|
|
|
|
|
|
| 01/25/20 |
54406 |
10-432 |
1 |
01/31/20 |
1/31/2020 |
|
|
| c. Other invalid data (e.g. text in numerical fields, dates out of range) |
|
|
|
|
|
|
| 01/04/20 |
A2454390 |
13-1241 |
2 |
01/30/20 |
2/6/2020 |
|
|
| c. Zeros -- replace with blanks |
|
|
|
|
|
|
| 02/10/20 |
A2454418 |
A34321 |
7 |
02/20/20 |
Not received |
|
|
| Step 2: Compute the Days Late (positive values) or Days early (negative values) |
|
|
|
|
|
|
| 01/23/20 |
A2454404 |
12-23451 |
2 |
02/18/20 |
Feb 5th, 2020 |
Note: This could just be fixed now. |
|
|
| Step 3: Remove outliers (save data for individual analysis of cause) |
|
|
|
|
|
|
| 01/13/20 |
A2454396 |
10-432 |
T |
01/19/20 |
1/19/2020 |
Note: May be a typo for QTY=5 or 6 -- look at your keyboard. |
|
|
| Step 4: Insert the Item name of each order |
|
|
| Step 5: Filter data by Part # showing only data for "12-23451" |
|
|
|
|
|
|
| Outliers (for later analysis of cause) |
|
|
| Step 6: Sort displayed data by "Days Late" (highest to lowest) |
|
|
|
|
|
|
|
| (insert data here with errors highlighted) |
|
|
|
|
|
|
|
|
|
|
| 01/31/20 |
A2454408 |
12-23451 |
1 |
02/26/20 |
8/19/2020 |
|
| Order date |
Invoice # |
Part # |
Qty |
Promised delivery date |
Actual Delivery Date |
Days Late (early) |
Item Name |
| 02/09/20 |
A2454417 |
10-432 |
2 |
02/15/20 |
5/15/2020 |
|
| 02/04/20 |
A2454413 |
12-23451 |
1 |
03/01/20 |
3/10/2020 |
9 |
Seat, driver, power, leather, black |
|
| 01/10/20 |
A2454394 |
12-23451 |
3 |
02/05/20 |
2/14/2020 |
9 |
Seat, driver, power, leather, black |
|
| 05/18/20 |
A2454484 |
12-23451 |
2 |
06/13/20 |
6/21/2020 |
8 |
Seat, driver, power, leather, black |
|
| 04/29/20 |
A2454477 |
12-23451 |
4 |
05/25/20 |
6/2/2020 |
8 |
Seat, driver, power, leather, black |
|
| 05/02/20 |
A2454479 |
12-23451 |
4 |
05/28/20 |
6/4/2020 |
7 |
Seat, driver, power, leather, black |
|
| 02/28/20 |
A2454431 |
12-23451 |
10 |
03/25/20 |
4/1/2020 |
7 |
Seat, driver, power, leather, black |
|
| 01/14/20 |
A2454397 |
12-23451 |
9 |
02/09/20 |
2/16/2020 |
7 |
Seat, driver, power, leather, black |
|
| 01/07/20 |
A2454391 |
12-23451 |
6 |
02/02/20 |
2/9/2020 |
7 |
Seat, driver, power, leather, black |
|
| 03/08/20 |
A2454436 |
12-23451 |
9 |
04/03/20 |
4/9/2020 |
6 |
Seat, driver, power, leather, black |
|
| 05/24/20 |
A2454486 |
12-23451 |
3 |
06/19/20 |
6/24/2020 |
5 |
Seat, driver, power, leather, black |
|
| 02/10/20 |
A2454419 |
12-23451 |
1 |
03/07/20 |
3/11/2020 |
4 |
Seat, driver, power, leather, black |
|
| 04/06/20 |
A2454463 |
12-23451 |
1 |
05/02/20 |
5/5/2020 |
3 |
Seat, driver, power, leather, black |
|
| 05/10/20 |
A2454481 |
12-23451 |
7 |
06/05/20 |
6/7/2020 |
2 |
Seat, driver, power, leather, black |
|
| 05/15/20 |
A2454483 |
10234 |
78 |
05/20/20 |
5/21/2020 |
1 |
Screw, #8x12, T34 Stainless |
|
| 04/28/20 |
A2454476 |
10234 |
51 |
05/03/20 |
5/4/2020 |
1 |
Screw, #8x12, T34 Stainless |
|
| 04/11/20 |
A2454467 |
12-23451 |
1 |
05/07/20 |
5/8/2020 |
1 |
Seat, driver, power, leather, black |
|
| 03/28/20 |
A2454454 |
10-432 |
2 |
04/03/20 |
4/4/2020 |
1 |
Display, Premium, 8" touch |
|
| 03/25/20 |
A2454451 |
10-432 |
5 |
03/31/20 |
4/1/2020 |
1 |
Display, Premium, 8" touch |
|
| 03/23/20 |
A2454449 |
10-432 |
5 |
03/29/20 |
3/30/2020 |
1 |
Display, Premium, 8" touch |
|
| 05/24/20 |
A2454485 |
10234 |
68 |
05/29/20 |
5/29/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 05/12/20 |
A2454482 |
10234 |
45 |
05/17/20 |
5/17/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 05/01/20 |
A2454478 |
A34321 |
3 |
05/11/20 |
5/11/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 04/23/20 |
A2454473 |
10234 |
64 |
04/28/20 |
4/28/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 04/19/20 |
A2454471 |
12-23451 |
7 |
05/15/20 |
5/15/2020 |
0 |
Seat, driver, power, leather, black |
|
| 04/17/20 |
A2454470 |
10234 |
61 |
04/22/20 |
4/22/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 04/13/20 |
A2454468 |
A34321 |
8 |
04/23/20 |
4/23/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 04/13/20 |
A2454469 |
10-432 |
3 |
04/19/20 |
4/19/2020 |
0 |
Display, Premium, 8" touch |
|
| 04/11/20 |
A2454465 |
10234 |
41 |
04/16/20 |
4/16/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 04/11/20 |
A2454466 |
A34321 |
3 |
04/21/20 |
4/21/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 04/07/20 |
A2454464 |
10-432 |
4 |
04/13/20 |
4/13/2020 |
0 |
Display, Premium, 8" touch |
|
| 04/05/20 |
A2454461 |
A34321 |
4 |
04/15/20 |
4/15/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 04/05/20 |
A2454462 |
10-432 |
3 |
04/11/20 |
4/11/2020 |
0 |
Display, Premium, 8" touch |
|
| 04/04/20 |
A2454459 |
A34321 |
2 |
04/14/20 |
4/14/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 04/04/20 |
A2454460 |
10-432 |
4 |
04/10/20 |
4/10/2020 |
0 |
Display, Premium, 8" touch |
|
| 04/02/20 |
A2454458 |
10-432 |
5 |
04/08/20 |
4/8/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/31/20 |
A2454456 |
10234 |
96 |
04/05/20 |
4/5/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 03/29/20 |
A2454455 |
A34321 |
7 |
04/08/20 |
4/8/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 03/27/20 |
A2454452 |
10-432 |
5 |
04/02/20 |
4/2/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/24/20 |
A2454450 |
10234 |
67 |
03/29/20 |
3/29/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 03/23/20 |
A2454448 |
A34321 |
9 |
04/02/20 |
4/2/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 03/21/20 |
A2454447 |
10-432 |
5 |
03/27/20 |
3/27/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/19/20 |
A2454444 |
10-432 |
3 |
03/25/20 |
3/25/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/15/20 |
A2454442 |
A34321 |
4 |
03/25/20 |
3/25/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 03/13/20 |
A2454440 |
10-432 |
4 |
03/19/20 |
3/19/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/12/20 |
A2454439 |
10234 |
69 |
03/17/20 |
3/17/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 03/09/20 |
A2454438 |
10-432 |
3 |
03/15/20 |
3/15/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/06/20 |
A2454434 |
A34321 |
4 |
03/16/20 |
3/16/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 03/06/20 |
A2454435 |
10-432 |
1 |
03/12/20 |
3/12/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/01/20 |
A2454432 |
10-432 |
1 |
03/06/20 |
3/6/2020 |
0 |
Display, Premium, 8" touch |
|
| 03/01/20 |
A2454433 |
10234 |
31 |
03/06/20 |
3/6/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 02/28/20 |
A2454430 |
A34321 |
4 |
03/09/20 |
3/9/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 02/20/20 |
A2454426 |
10-432 |
5 |
02/26/20 |
2/26/2020 |
0 |
Display, Premium, 8" touch |
|
| 02/18/20 |
A2454425 |
10234 |
67 |
02/23/20 |
2/23/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 02/15/20 |
A2454421 |
10234 |
57 |
02/20/20 |
2/20/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 02/15/20 |
A2454423 |
10-432 |
5 |
02/21/20 |
2/21/2020 |
0 |
Display, Premium, 8" touch |
|
| 02/12/20 |
A2454420 |
10-432 |
4 |
02/18/20 |
2/18/2020 |
0 |
Display, Premium, 8" touch |
|
| 02/08/20 |
A2454416 |
10234 |
1 |
02/13/20 |
2/13/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 02/05/20 |
A2454414 |
A34321 |
5 |
02/15/20 |
2/15/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 02/03/20 |
A2454412 |
10-432 |
|
|
| 0 |
Display, Premium, 8" touch |
|
| 02/01/20 |
A2454410 |
10234 |
48 |
02/06/20 |
2/6/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 01/31/20 |
A2454409 |
10-432 |
4 |
02/06/20 |
2/6/2020 |
0 |
Display, Premium, 8" touch |
|
| 01/28/20 |
A2454407 |
A34321 |
9 |
02/07/20 |
2/7/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 01/24/20 |
A2454405 |
A34321 |
2 |
02/03/20 |
2/3/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 01/22/20 |
A2454403 |
10-432 |
5 |
01/28/20 |
1/28/2020 |
0 |
Display, Premium, 8" touch |
|
| 01/18/20 |
A2454401 |
10234 |
13 |
01/23/20 |
1/23/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 01/17/20 |
A2454400 |
10-432 |
|
|
| 0 |
Display, Premium, 8" touch |
|
| 01/08/20 |
A2454392 |
10-432 |
4 |
01/14/20 |
1/14/2020 |
0 |
Display, Premium, 8" touch |
|
| 01/08/20 |
A2454393 |
A34321 |
2 |
01/18/20 |
1/18/2020 |
0 |
Bolt, 1/4", T34 Stainless |
|
| 01/02/20 |
A2454385 |
12-23451 |
8 |
01/28/20 |
1/28/2020 |
0 |
Seat, driver, power, leather, black |
|
| 01/02/20 |
A2454386 |
10234 |
83 |
01/07/20 |
1/7/2020 |
0 |
Screw, #8x12, T34 Stainless |
|
| 01/02/20 |
A2454388 |
10-432 |
2 |
01/08/20 |
1/8/2020 |
0 |
Display, Premium, 8" touch |
|
| 05/04/20 |
A2454480 |
A34321 |
7 |
05/14/20 |
5/13/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 04/24/20 |
A2454474 |
A34321 |
10 |
05/04/20 |
5/3/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 04/22/20 |
A2454472 |
A34321 |
8 |
05/02/20 |
5/1/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 03/31/20 |
A2454457 |
A34321 |
3 |
04/10/20 |
4/9/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 03/20/20 |
A2454445 |
A34321 |
10 |
03/30/20 |
3/29/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 03/09/20 |
A2454437 |
A34321 |
2 |
03/19/20 |
3/18/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 02/23/20 |
A2454428 |
A34321 |
10 |
03/04/20 |
3/3/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 02/23/20 |
A2454429 |
10-432 |
4 |
03/04/20 |
3/3/2020 |
-1 |
Display, Premium, 8" touch |
|
| 02/15/20 |
A2454422 |
A34321 |
1 |
02/25/20 |
2/24/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 02/02/20 |
A2454411 |
A34321 |
5 |
02/12/20 |
2/11/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 01/17/20 |
A2454398 |
A34321 |
2 |
01/27/15 |
1/26/2015 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 01/02/20 |
A2454387 |
A34321 |
5 |
01/12/20 |
1/11/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 01/02/20 |
A2454389 |
A34321 |
8 |
01/12/20 |
1/11/2020 |
-1 |
Bolt, 1/4", T34 Stainless |
|
| 03/28/20 |
A2454453 |
12-23451 |
1 |
04/23/20 |
4/20/2020 |
-3 |
Seat, driver, power, leather, black |
|
| 03/18/20 |
A2454443 |
12-23451 |
4 |
04/13/20 |
4/9/2020 |
-4 |
Seat, driver, power, leather, black |
|
| 03/14/20 |
A2454441 |
12-23451 |
6 |
04/09/20 |
4/4/2020 |
-5 |
Seat, driver, power, leather, black |
|
| 02/17/20 |
A2454424 |
12-23451 |
3 |
03/14/20 |
3/8/2020 |
-6 |
Seat, driver, power, leather, black |
|
| 01/17/20 |
A2454399 |
12-23451 |
1 |
02/12/20 |
2/6/2020 |
-6 |
Seat, driver, power, leather, black |
|
| 02/21/20 |
A2454427 |
12-23451 |
3 |
03/18/20 |
3/10/2020 |
-8 |
Seat, driver, power, leather, black |
|
| 05/31/20 |
A2454487 |
12-23451 |
10 |
06/26/20 |
6/17/2020 |
-9 |
Seat, driver, power, leather, black |
|
| 03/21/20 |
A2454446 |
12-23451 |
7 |
04/16/20 |
4/7/2020 |
-9 |
Seat, driver, power, leather, black |
|
| 04/27/20 |
A2454475 |
12-23451 |
10 |
05/23/20 |
5/13/2020 |
-10 |
Seat, driver, power, leather, black |
|
| 02/05/20 |
A2454415 |
12-23451 |
4 |
03/02/20 |
2/21/2020 |
-10 |
Seat, driver, power, leather, black |
|
| 01/20/20 |
A2454402 |
12-23451 |
4 |
02/15/20 |
2/5/2020 |
-10 |
Seat, driver, power, leather, black |
RVP2021
M1Datacleanandsort--SOLUTIONLearningactivity2.xlsx
