Excel Order Management Automation

Objective: Automate and streamline order data management from EcomStore, FastShip, and QuickDeliver.

Instructions:

Step 1: Consolidate Files

Open files: EcomStore_Orders.csv, FastShip_Orders.csv, QuickDeliver_Orders.csv.

Create a single file with tabs named: "EcomStore," "FastShip," and "QuickDeliver."

Step 2: Format Date in EcomStore

Change date format in Column F ("Fulfilled Date") to dd-mm-yyyy.

Insert two temporary columns next to Column F.

Use "Text to Columns" to split data, then delete temporary columns.

Step 3: Fill Missing Data

For multi-product orders, fill blank fields in "Fulfilled Quantity," "Total Amount," and "Billing Region" using Go To > Special > Blanks and formula = (Up Arrow) + Ctrl+Enter.

Step 4: Normalize Order IDs

In FastShip tab, create a column to append # to order ID using formula: ="#"&A2

Step 5: Add Order Status to EcomStore

Create columns "QuickDeliver Status" and "FastShip Status."

Use VLOOKUP for status:

QuickDeliver: =VLOOKUP(A2, QuickDeliver!C$1:S$225, 17, FALSE)

FastShip: =VLOOKUP(A2, FastShip!B$1:E$2492, 4, FALSE)

Step 6: Filter and Copy Delivered Status

Filter "FastShip Status" for "Delivered" and "Out for Delivery."

Copy data to "FastShip Delivered" sheet.

Filter "QuickDeliver Status" for "Delivered" and copy to "QuickDeliver Delivered."

Step 7: Create Import Format for QuickDeliver Delivered

Create "Import QuickDeliver" sheet.