how to get this join

[Inactive User]

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!

[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.

[Inactive User]

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? 

[DavidP]

If you can provide details of your full requirement, we'll be able to help much better.

[Inactive User]

I have customers from 2 files (i.e., unique customers), with age, first and last name, in each of the files. I want to find customers that went to one place but not the other, only and their average age? 

[DavidP · Accepted solution]

This is how I would do it