How to left-join crm customer data with order history from two excel files
- Step 1Export CRM customers and orders — Download the customer list and the order history as two separate
.xlsxor.csvfiles. Make sure each table sits on the first sheet of its file. - Step 2Open the Sheet Joiner — Load the Multi-Sheet Joiner. Two drop zones appear — File A (left) and File B (right).
- Step 3Drop CRM as File A, orders as File B — File A is the customers (the rows you want to keep); File B is the orders (the data to attach).
- Step 4Set the Customer ID key on each side — Put the CRM's customer-id header in
leftKeyand the orders file's customer-id header inrightKey. They can be spelled differently. - Step 5Leave joinType on left — Keep
leftso customers with no orders are retained. Switch toinneronly if you want active buyers exclusively. - Step 6Run and download — Process and download
joined-data.xlsx. The metrics panel shows input customers, output rows, and runtime — output rows exceed customers when buyers have multiple orders.
CRM/orders join setup
Typical configuration for a customer-to-orders left join. Headers shown are examples — type whatever your real exports use.
| Setting | Value | Why |
|---|---|---|
| File A | CRM customer export | Left table — every customer row is preserved |
| File B | Orders / ERP export | Right table — order columns are appended |
leftKey | CustomerID (CRM header) | Identifies the customer key in File A |
rightKey | cust_id (orders header) | Customer key in File B; may differ from File A's name |
joinType | left | Keep customers with zero orders (blank order cells) |
What each customer scenario produces
Output shape per customer under a left join. 'Fan-out' rows are expected, not duplicates to delete.
| Customer in CRM | Orders in File B | Rows in output |
|---|---|---|
| Active buyer | 1 order | 1 row, order columns filled |
| Repeat buyer | 3 orders | 3 rows (one per order), customer fields repeated |
| New / dormant | 0 orders | 1 row, order columns blank |
| Order with no CRM record | n/a (right-only) | 0 rows — right-only orders are dropped |
Tier limits
A CRM-plus-orders join needs two files, so Free (1 file) cannot run it. Pick the tier that covers your larger export.
| Tier | Max size / file | Max rows / file | Files |
|---|---|---|---|
| Free | 5 MB | 10,000 | 1 (cannot join) |
| Pro | 50 MB | 100,000 | 5 |
| Pro-media | 200 MB | 500,000 | 20 |
| Developer | 500 MB | Unlimited | Unlimited |
Cookbook
CRM and order fragments, anonymised. Customer ID is matched as a string, so numeric CRM ids join to text order ids without a helper column.
Customer with no orders is kept (left join)
A left join preserves the dormant customer with blank order columns — exactly what you want for a 'customers and their orders, including non-buyers' report.
File A (CRM): File B (orders): CustomerID,name cust_id,order_no,total 100,Acme Co 100,SO-1,500 101,Globex 101,SO-2,250 102,Initech (no orders) Left join (leftKey=CustomerID, rightKey=cust_id): CustomerID,name,order_no,total 100,Acme Co,SO-1,500 101,Globex,SO-2,250 102,Initech,, <- kept, order columns blank
Repeat buyer fans out to one row per order
Acme has three orders, so the customer row appears three times — the correct grain for summing revenue per customer in a pivot.
File A (1 customer): File B (3 orders):
CustomerID,name cust_id,total
100,Acme Co 100,500
100,250
100,90
Left join output (3 rows):
CustomerID,name,total
100,Acme Co,500
100,Acme Co,250
100,Acme Co,90Inner join for active buyers only
Switch to inner join to drop customers with no orders entirely — useful for an 'active accounts' segment.
joinType: inner Output (Initech excluded): CustomerID,name,order_no,total 100,Acme Co,SO-1,500 101,Globex,SO-2,250
Numeric CRM id meets text order id
CRM exports the id as a number, the order system as text. String coercion makes them match, no =TEXT() helper needed.
File A: File B: CustomerID,name cust_id,total 5001,North Wind "5001",120 5002,South Bay "5002",75 Left join: CustomerID,name,total 5001,North Wind,120 5002,South Bay,75
Email column appears in both files — orders value wins
If both exports carry an email column, the orders file's value overwrites the CRM's in the merged row. Rename one before joining if the CRM email is the authoritative one.
File A (CRM): File B (orders): CustomerID,email cust_id,email 9,a@crm.com 9,a@shop.com Left join output: CustomerID,email 9,a@shop.com <- orders email overwrote CRM email
Edge cases and what actually happens
Customer with multiple orders appears multiple times
ExpectedA left join emits one row per matching order, so a 3-order customer shows 3 times with the customer fields repeated. This is the right grain for per-order analysis; if you need one row per customer, summarise with a pivot generator afterwards.
Customer ID stored as number vs text
PreservedCRM and order systems often disagree on the id type. Keys are compared as strings, so 5001 and "5001" match — no helper column or =TEXT() wrapper required, unlike VLOOKUP.
Order with no matching CRM record
DroppedOrders whose customer id is absent from the CRM file are right-only rows and never appear in the output (this is a left/inner join, not full outer). To audit orphan orders, run the join the other way round (orders as File A) and look for blank CRM columns.
Customer ID column misspelled in a key field
Invalid keyIf leftKey or rightKey does not exactly match the real header (including a trailing space), that side resolves to empty for every row. A left join then keeps all customers with blank order columns; an inner join returns nothing. Copy the header text exactly.
Email or name column collides
Orders winsAny non-key column present in both files is overwritten by File B's value, since every right column except the key is written into the merged row. If the CRM field is authoritative, rename the orders column first with the header rename tool.
CRM export has the table on sheet 2
Sheet 1 onlyOnly the first worksheet of each file is read. A multi-tab CRM workbook whose customer table is not on tab 1 will be misread. Move the table to the first sheet or export it standalone before joining.
Free tier
Run rejectedThe join needs two files and is Pro-minimum, so Free (1 file) cannot run it. The tool returns 'Sheet Joiner requires Pro tier.' Upgrade to Pro for 50 MB / 100,000-row exports.
Blank customer ids in either file
By designRows with empty Customer ID all coerce to "" and match each other, which can cross-join every blank-id customer to every blank-id order. Filter out blank ids first to avoid an inflated, meaningless result.
Two separate order files
WorkaroundThe joiner takes exactly two files. To attach two order sources, concatenate the order exports into one file first (stack the rows), then join the combined orders file to the CRM.
Output much larger than the CRM file
ExpectedOutput rows = sum of matches across customers, so a CRM of 1,000 customers averaging 4 orders each yields ~4,000 rows. The metrics panel shows input vs output counts so the expansion is transparent.
Frequently asked questions
What if one customer has multiple orders?
A left join produces one output row per matching order. A customer with three orders appears three times, with the customer columns repeated and a different order on each row — the correct grain for per-order reporting.
How is this better than VLOOKUP?
VLOOKUP returns one matched value per formula and breaks on number-vs-text key mismatches. The joiner appends all File-B columns in one pass, handles one-to-many relationships by fanning out, and matches keys as strings so 5001 joins to "5001".
Can I do an inner join to see only customers with orders?
Yes. Switch joinType to inner and only customers whose id exists in the orders file are kept — dormant customers are dropped.
The Customer ID header is different in each file — does that matter?
No. Set leftKey to the CRM's header and rightKey to the orders file's header. Only the values have to match, not the column names.
Which file should be A and which B?
File A is the table whose rows you want to keep — usually the CRM customers, so a left join retains every customer. File B is the orders file, whose columns get appended.
What happens to orders that have no customer in the CRM?
They are dropped. This is a left/inner join, so right-only rows never surface. To find orphan orders, re-run with the orders file as File A and look for blank CRM columns.
Are columns that exist in both files duplicated?
No. File B's key column is removed, and any other shared column is overwritten by File B's value. If the CRM version should win, rename the orders column first with the header rename tool.
How do I collapse the fanned-out rows back to one per customer?
Run the join to get the per-order rows, then feed the result into the pivot generator to aggregate (sum totals, count orders) per Customer ID.
Is the customer data uploaded to a server?
No. Parsing and joining happen entirely in your browser with SheetJS. The customer and order files never leave the tab — suitable for PII-sensitive data.
What tier do I need?
Pro. The join needs two files, which is above the Free tier's 1-file limit, and the tool is Pro-minimum. Pro covers 50 MB / 100,000 rows per file.
Can I redact PII before sharing the joined file?
Yes — run the merged workbook through the email/phone scrubber at the PII scrubber to mask emails and phone numbers before distributing it.
What is in the output file?
A values-only .xlsx (joined-data.xlsx, sheet Joined) — no VLOOKUP, no Power Query connection, nothing to refresh. Just merged rows ready for a pivot or BI import.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.