Retaining Duplicates in Comparison of 2 Files using Join Tool

[FChen1]

Alteryx Community - I am trying to compare two very similar files to determine if there are any records from one file that is not present in the other file (and vice versa).  Thus, using Join tool to identify "L" and "R" outputs. However, the issue with the two files is that there are multiple records that are exactly the same.  There is not a unique identifier in either file or even multiple fields that can be "concatenated" to make a unique identifier.  Further, there are "duplicates" of the same records in both files, but more importantly, these duplicate records cannot be removed through a Unique tool, as an example.  The data in each of these duplicate records is necessary to validate the total amount.

For example, below, there are 7 records in the "L" and "R" input files. Two of the records are duplicates (B 9 and B($) 9). Cannot remove the duplicate record because the total sum at the bottom will be incorrect.

Any suggestions on how I can compare the "L" and "R" input files, identify the "L" and "R" specific outputs, join them together to make the Final Desired Output?  

Thank you!

[phottovy · Accepted solution]

Hi @FChen1 ,

Here is one possible option. I added sorts and multi-row tools to both sides. For each duplicate value, I create a unique row ID and then use that in my join. This allows you to join on each duplicate row that exists on both sides without creating a bunch of extra rows on the join.

[FChen1]

Thank you!