Finding common rows of data between two separate Excel files (ignoring position)

[angeleneveloce]

Hello,

I am brand new to Alteryx. I need to construct a workflow that is able to scan through an Excel worksheet and find the same exact rows of data as the rows provided in a separate Excel file's worksheet. The Excel files I am working with contain information on monthly transactions. Excel file #1 is the master file/ general ledger that contains all the transactions that have occurred, whether they have been cleared or not. It is just a historical record of transactions. Excel file #2 contains information from the past month of transactions whose due date for review is approaching or has already approached. Transactions are identified by columns containing account #, customer ID, and transaction amount. If a transaction listed in file #1 is also listed in file #2, then that means the transaction was not cleared yet in that month. If the transaction is NOT listed in file #2, then it means the transaction has been cleared. My task is to identify which transactions listed in master file #1 are also in file #2, and return an output column in file #1 stating "cleared" if the row of data is not present/matching and "not cleared" if the row of data is present/matching. The row of data does not have to be in the same position, as long as it is somewhere in the second file and exactly matching. Below is an example.

Before Workflow:

Master File #1

Account #	Customer ID	Amount
10001	ab12	$100
20001	cd34	$200
30001	ef56	$300
40001	gh78	$400

File #2 Monthly Transactions

Account #	Customer ID	Amount
30001	ac93	$101
40001	gh78	$400
20001	jn83	$823
10013	cd34	$200

After Workflow:

Master File #1

Account #	Customer ID	Amount	Cleared? (NOT in File #2?)
10001	ab12	$100	Cleared
20001	cd34	$200	Cleared
30001	ef56	$300	Cleared
40001	gh78	$400	Not Cleared

The row of data highlighted in red originally listed in file #1 is ALSO LISTED in file #2, even though the row of data is not in the same position. Therefore, the new result/output column in file #1 returns "Not Cleared" in the original row to indicate that the row of data was also found somewhere in file #2, and matching exactly.

I am not sure if I articulated my problem correctly, but if anyone could offer advice or help suggest a workflow that would be awesome. Thank you!

[DavidSkaife]

Hi @angeleneveloce 

This will work for you: Join the two datasets together on the three fields, and on the join using a formula tool to create the 'Not Cleared' text, then on the left side output (so the data left over from Master File 1) you do the same with another formula tool but use 'Cleared' instead. Finally union the two streams back together!

I've also attached a workflow for you to see how it works :)

[angeleneveloce]

Thank you for such a quick response! Looks good, I will try and implement it with my files tonight!