Hi, I have a sheet with thousands or rows showing transport statuses. (See example below) . The start (Sendt) and end status (Delivered) is always the same, but the number of steps between these statuses will wary. The id number (Showing in col D will always be available for all steps in a shipment, and will be unique.
How can I find all shipments that are completed? (has both the Sendt and Delivered status so these can be removed from the list, including all steps between Sendt and Delivered? )
How can I find all shipments that are completed? (has both the Sendt and Delivered status so these can be removed from the list, including all steps between Sendt and Delivered? )
Example.xlsx | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | 1245 | New York | Sendt | 55468 | ||
2 | 1245 | New York | Step 1 | 55468 | ||
3 | 1245 | New York | Step 2 | 55468 | ||
4 | 1245 | New York | Step 3 | 55468 | ||
5 | 1245 | New York | Step 4 | 55468 | ||
6 | 1245 | New York | Step 5 | 55468 | ||
7 | 1245 | New York | Step 6 | 55468 | ||
8 | 1245 | New York | Step 7 | 55468 | ||
9 | 1245 | New York | Delivered | 55468 | ||
10 | 1354 | Chicago | Sendt | 6657985 | ||
11 | 1354 | Chicago | Step 2 | 6657985 | ||
12 | 1354 | Chicago | Step 3 | 6657985 | ||
13 | 1354 | Chicago | Step 4 | 6657985 | ||
14 | 1354 | Chicago | Step 5 | 6657985 | ||
15 | 1445 | Seattle | Sendt | 5578223 | ||
16 | 1445 | Seattle | Step 1 | 5578223 | ||
17 | 1445 | Seattle | Step 2 | 5578223 | ||
18 | 1445 | Seattle | Step 3 | 5578223 | ||
19 | 1445 | Seattle | Step 4 | 5578223 | ||
20 | 1445 | Seattle | Step 5 | 5578223 | ||
21 | 1445 | Seattle | Step 6 | 5578223 | ||
22 | 1445 | Seattle | Step 7 | 5578223 | ||
23 | 1445 | Seattle | Delivered | 5578223 | ||
24 | 5589 | Dallas | Sendt | 997855 | ||
25 | 5589 | Dallas | Step 1 | 997855 | ||
26 | 5589 | Dallas | Step 2 | 997855 | ||
Sheet1 |