Why does joining two Excel tables create extra rows?
Find out why two orders become six rows after a join. Check duplicate customer IDs and add the missing condition to join each order to the right address.
If a join creates extra rows, check how often the matching ID appears in each sheet. Two orders matched to three addresses for the same customer produce 2 × 3 = 6 rows. Before deleting rows, check whether another condition is needed to select the intended address.
Two orders can have six valid matches
In this fictional example, customer C10 has orders O10 and O20. The address list contains Head office, Warehouse and Store for the same customer. Neither order includes an address code, so the customer ID alone cannot select the intended destination.
If you are used to a lookup returning the first match, the extra rows can be surprising. A join in JOIN SHEET returns every matching pair. In this case, it shows all three addresses because Customer ID alone gives no reason to prefer one.
Inspect the keys before removing rows
- Join Customer ID in Orders to Registered ID in the Address sheet and choose Matching rows only.
- In Result preview, check how many times O10 and O20 appear.
- Open Check join from the line menu and run the check.
- Expand the detailed counts. The two order rows and three address rows all belong to a repeated key; there is one repeated key value on each side.
- Look up C10 in the source sheets to see the repeated orders and addresses. Check join gives you counts; inspect the source sheets to identify the actual records.
Expected result
| Order number | Customer ID | Address |
|---|---|---|
| O10 | C10 | Head office |
| O10 | C10 | Warehouse |
| O10 | C10 | Store |
| O20 | C10 | Head office |
| O20 | C10 | Warehouse |
| O20 | C10 | Store |
The order number repeats, but the address differs. Keeping one arbitrary row per order could discard the address that should have been used.
How can I get one intended address per order?
If each order includes an address code, join on both customer ID and address code. If you need only the default address, filter the address sheet to those records first. When neither field is available, decide which address each order should use before continuing.
Check join inspects the selected sheet pair. Its counts can differ from the final result after other joins or result refinements. After changing the keys, recheck the final row count and a few specific orders.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
Why do multiple-table joins produce duplicate rows? — Stack Overflow
Power query merge tables results in duplicate rows — Reddit r/excel
The order-and-address example is fictional and illustrates the matching problem in these discussions.
