Excel Order Management Automation

Ilija Pavlovic

0

Data Engineer

JavaScript

Microsoft Excel

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.
Like this project
0

Step 1: Consolidate Files Step 2: Format Date in EcomStore Step 3: Fill Missing Data Step 4: Normalize Order IDs Step 5: Add Order Status to EcomStore

Likes

0

Views

2

Tags

Data Engineer

JavaScript

Microsoft Excel

Ilija Pavlovic

Python Data Engineer & Pentaho Data Integration API

Data Engineer / Data Analyst for Sports Analytics
Data Engineer / Data Analyst for Sports Analytics
Consolidating CSV Files for Centralized Loading
Consolidating CSV Files for Centralized Loading
Integrating Data via Web Services API and JSON Parsing
Integrating Data via Web Services API and JSON Parsing
Migrating Data from Oracle DB to E-Commerce Platform
Migrating Data from Oracle DB to E-Commerce Platform