Joining two data sets with different data joined by one field

Hi Community!

I'm trying to join two data sets with clients A-C in one set and clients A-F in the other using MONTH as common field. I tried the Join tool followed by the Union tool, but this comes back with double entries since the number of unique clients are different in the two data sets. I've attached a mock up and screen shots.

Desired final result:

Sales under $5

Sales over $5

Any ideas welcome! Many thanks!

Mockup.xlsx

Workflow

Datasets

Accepted answers

atcodedog05

Hi @Savannah

Here is how you can do it. It is advisable to have join key pair as unique combo.

Workflow:

1. I am first using a summarize tool to sum up USD for each month and each client for under 5 USD and over 5 USD. This creates Month & Client unique pair.

2. Using multi join tool outer joining on Month & Client.

3. Using formula tool to fill month and client to create single reference columns.

Hope this helps 🙂

client.yxmd

All comments

atcodedog05

Hi @Savannah

Here is how you can do it. It is advisable to have join key pair as unique combo.

Workflow:

1. I am first using a summarize tool to sum up USD for each month and each client for under 5 USD and over 5 USD. This creates Month & Client unique pair.