How You Can Do This in Alteryx (Step-by-Step)
1. Bring in both datasets Use Input Data tools to pull in Side 1 and Side 2. Clean the CUSIP and ISIN fields (trim spaces, upper case) using Data Cleansing or Formula tools.
2. Add Record IDs Use the Record ID tool to track individual rows — this helps a lot later when you're generating combinations or checking for unmatched records.
3. Group by CUSIP (or ISIN) Use the Summarize tool to group by CUSIP. You'll be working within each group for generating combinations.
4. Generate Combinations This is the tricky part: Use Batch Macros or Generate Rows + Append Fields logic to create all possible combinations within the CUSIP (or ISIN) group.
Example: Take row 1 from Side 1 and pair it with every possible 1-row, 2-row, 3-row combination from Side 2 that shares the same CUSIP.
Do the reverse too — 1 row from Side 2 vs combinations from Side 1.
Alteryx doesn’t natively support “combination matching,” so you may need to build or adapt a Batch Macro.
There are good examples on the Alteryx Community , I’ll link them below.
5. Compare Combinations Once combinations are built, apply your matching logic (e.g., total amount, quantity, etc.) using Join or Filter tools.
6. Output Matches and Remainders From your matches, pull out: Rows from both sides that matched. Then use Join’s Left Unjoined and Right Unjoined outputs to get: What’s left from Side 1 What’s left from Side 2
Reference Links:
Solved: Merge multiple rows into one row - Alteryx Community
Solved: Join by Multiple Fields with >= or <= Criteria - Alteryx Community
Solved: Row Level Fuzzy Matching - Alteryx Community
Solved: Reconciliation of two Data sets - Alteryx Community
Solved: Re: Joining datasets, but once a match is made, it... - Alteryx Community
