how to get this join

I have 2 excel sheets with customer data (name, and age)

I am trying to merge these 2 together such that I want to find the customers in one sheet but not in the other

I started with:

to get the matching ones then thinking of using a filter after the join for "False" to identify those that are dissimilar between the 2 sheets.

However this does not work and wondering how to get entries in one sheet but not the other and vice versa? I know I shoud start with a join and also tried a union but no luck. Thanks!

Output

Accepted answers

DavidP

This is how I would do it

Unjoined records.yxmd

All comments

DavidP

If you join 2 datasets with the join tool, using a common identifier field between the 2 such as Customer Name of Customer ID, the J output will show all the records that have matched between the to datasets. This joined data will contain the fields/columns from both data sets, so you can do further checks to see what the differences between the other fields are, for example.

The L or Left output will show the records that were in the Left input, but that did not match to anything in the Right input and the same for the R or Right Output.

The records from the L and R outputs are therefore the records from each dataset that are missing in the other.

Hope this makes sense.

unknown

I tried that but then wondering if I need a union or not? I have the customers from the L and the customers from the R and then need an average of customer age in one of the places/sheet but not in the other? So I think of a weighted average but something is missing?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317