Merge spreadsheets, even when the columns don't line up
Combining files from different people or systems usually means "E-mail" in one, "Email address" in another and dates in different formats. Attach the files and this to-do has your todo.is agent map the columns, either stack the rows or join them on a shared key, and give you one clean workbook plus a report of rows that didn't match.
The prompt
- Merge the attached spreadsheets: [FILES]. I want to [STACK OR JOIN]. Match columns with the same meaning even if the names differ (e.g. "E-mail" and "Email address"), and show me the column mapping you used. If joining, match rows on [KEY COLUMN], ignoring case and extra spaces. Use [DATE FORMAT] for all dates. Add a "Source file" column so I can see where each row came from. Remove exact duplicate rows. Put the result on a "Merged" sheet, rows that only exist in one file on an "Unmatched" sheet, and the column mapping on a "Mapping" sheet. Return one .xlsx file named [OUTPUT NAME].
What to change
- [FILES]: Attach the files and list them, e.g. "jan.xlsx, feb.xlsx and mar.csv" or "orders.xlsx and customers.xlsx".
- [STACK OR JOIN]: "stack them into one long list" (same kind of data, e.g. monthly exports) or "join them side by side" (e.g. add customer details to orders).
- [KEY COLUMN]: The shared column, e.g. "Customer ID" or "Email". Write "none" if stacking.
- [DATE FORMAT]: e.g. "YYYY-MM-DD".
- [OUTPUT NAME]: e.g. "Q1_Orders_Merged".
Example result
- Q1_Orders_Merged.xlsx (example)
- Files: orders_jan.xlsx (2,010 rows), orders_feb.xlsx (1,876), orders_mar.csv (2,301), customers.xlsx (1,422)
- Mode: stacked the three order files, then joined customer details on Customer ID
- Mapping sheet
- • Order ID ← "Order ID" (jan, feb), "order_number" (mar)
- • Order date ← "Date" (jan), "Order date" (feb), "created_at" (mar). All converted to YYYY-MM-DD
- • Customer ID ← "Cust ID" (jan, feb), "customer_id" (mar), "ID" (customers)
- • Amount ← "Total" (jan, feb), "amount_gross" (mar). Text like "€1.240,50" converted to 1240.50
- • Email ← from customers.xlsx "E-mail"
- • Source file added
- Merged sheet
- • 6,187 order rows stacked, 47 exact duplicates removed (the same orders exported in both Feb and Mar) → 6,140 rows
- • 6,057 rows matched to a customer and got Name, Email and City
- Unmatched sheet
- • 83 orders with a Customer ID not found in customers.xlsx (mostly IDs above 9,000, which look newer than the customer export)
- • 212 customers with no order in Q1 (kept here in case you want a win-back list)
- Checks
- • Total amount before merging: 412,880.40. After removing the 47 duplicates: 409,115.90. The difference equals the duplicate orders
- • Columns only in one file (e.g. "Coupon" in March) are kept, empty for other months
- Reply "export the 212 customers with no order as CSV" for a follow-up list.
How to do it with todo.is
- Attach all the files, then copy the prompt and say whether to stack or join.
- Send it on the Today screen in todo.is, or attach the files to your agent on WhatsApp.
- Your agent maps the columns, merges the rows and checks the totals.
- Look at the Mapping and Unmatched sheets, then ask for fixes or a different key column.
Tips for a better result
- Stack files with the same kind of rows (monthly exports). Join files that describe different things (orders and customers).
- A shared ID is much safer than names for joining. Names have typos, IDs usually don't.
- Ask for a total check (sum before and after) so you can trust that nothing was lost or doubled.
- Need it every month? Say "every 1st of the month, merge the exports I email you the same way".
merge spreadsheets: FAQ
- How do I merge two Excel spreadsheets into one? To stack similar files, copy rows under each other with matching columns, or use Power Query's Append. To combine details, use XLOOKUP or Power Query's Merge on a shared key. Your agent can do either and hand you the file.
- What if the column names are different? Your agent matches columns by meaning, like "E-mail" and "Email address", and shows the mapping so you can correct it.
- Can it merge CSV and Excel files together? Yes. Mix .csv, .xlsx and .xls files, and get one .xlsx or .csv back.
- Can I merge sheets inside one workbook? Yes. Attach the workbook and say "merge all sheets into one", and a Source sheet column is added to each row.
JavaScript is required to use the todo.is app.