Matching rows across multiple Input Data and then combining the Outputs

[MannyNay]

Hello,

I was looking around the community here for a solution to see if someone encountered something similar. I see it in bits and parts but not the entirety and I was hoping someone could help 🙂 

I am pretty new to using Alteryx and have been taking the training courses but need some assistance on an Ask, please:

What I Have:

• Two files. File "A" and File "B."
• The data is a mix of letter and numbers so I will likely be using "String." 
• File "A" has substantially more columns and rows. Fortunately, the headers I need to look at are the same on both, so I care about the same amount of columns on both (we can call them Column X, Column Y, Column Z).

What I Need: 

• Find if a particular row in File "A" matches with a corresponding row in File "B." Meaning, does the data in File "A" for an entry, horizontally across the columns I'm looking at, match an entry across all the columns in File "B"?
• Or, at least, can an entry in File "A" match at least some of the data points in the row/column in File "B" (by hierarchy in terms of importance -- search by column X first, then column Y, column Z, etc)?
• Extract the rows that match exactly in File "A" and File "B" and put it into a combined output.
• Then extract the rows that partially match, into another output.
• Then combine both those outputs. 

What I Tried: 

Two files of "Input Data." --> Join (to see what matched in one column) --> Output Data

Two files of "Input Data." --> Formula (to concatenate; To Combine the data to try and see what matched in multiple columns) --> Output Data

I'm about to try the coding/formula needed to search by hierarchy (if, and) in Alteryx. 

Is there a smoother way of combining all of that to transform it into one straight workflow? 

I appreciate your help! 

[T_Willins · Accepted solution]

Hi @MannyNay,

You were on the right track with using the Join tool.  The initial Join compares Columns X, Y, and Z at the same time.  The subsequent Join tools take the unmatched outputs from the previous Join tool and match Column X, then Column Y, then Column Z.  Finally the information is grouped, tagged with a sheet name field, then Output to a single Excel file.  You may need to make some minor adjustments for your actual data.  Let me know if you have questions.

[MannyNay]

Hi T_Willins,

Your Workflow and detailed breakdown was such a great help! Thank you for helping guide me in the right direction. Seeing your Workflow, it makes sense, especially the use of the Union tools.