Combination Match

Gunjan_Chhabria

If I have two sets of data side 1 and side 2, and I want to match the data basis CUSIP first, from all combinations i.e., 1st row of side 1 vs all rows of side 2, then vs all combinations of 2 rows of side 2, then 3 rows and so on. Then also try the reverse i.e., 1 of side 2 vs all of side 1 for that CUSIP, then all possibile combinations of 2 rows for the CUSIP in side 2 and so on. Then do the same for ISIN.

I want the output to give me what has been joined for both sides, what is left in side 2 and what is left in side 1

Example of Inputs.pdf

VinodKammula28

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