Compare two excel data and result in one excel with multiple sheet

Question

Hello All any one can help me with below problem

I have two Excel files containing multiple columns, with Account Number and FB ID being the same in both files.

- Sheet 1 of the output Excel should contain all the data from Excel 1.
- Sheet 2 should contain all the data from Excel 2.
- Sheet 3 should contain data from Excel 1. It should compare each FB ID with all FB IDs in Excel 2, and if a match is found, it should be marked as "Match" in a newly created column next to it.
- Similarly, Sheet 4 should contain data from Excel 2. It should compare each FB ID with all FB IDs in Excel 1, and if a match is found, it should be marked as "Match" in a newly created column next to it.

rohit782192 · Answer

I really like the solution. It is possible to get the Exception report as where is the Null Value.

adarsh2608 · Answer

Hello @LindonB  and @ChrisTX ,

I'm able to join the data, but i have ~100K of entries and each contains lot duplicates and it is taking lot of time ~3hr to run a small workflow

LindonB · Answer

To accomplish the table vs table check, you want to do a couple joins on the FB ID field. My attached example shows how you would do this with generated data matching your description.

Another thing you might be asking about is how to write the various outputs to Excel spreadsheets in the same workbook. You can just add several Data Output tools after the joins, but this can present some challenges, as if you reach another output before the file is closed on the backend, you can experience errors. Still, there's a few solutions....

* Use a Block Until Done tool but this has limited uses as it assumes the same incoming data table.
* Create a field such as "Output" and give each join result a value such as 'Sheet 3' and 'Sheet 4'. Then you would select "Change File/Table Name" in the dropdown of the Output Data tool; then select "Take File/Table Name from Field." However, this too won't work too well for you since the field names differ from output to output (sheet to sheet).
* The last option is to use Control Containers, which is a fairly new Alteryx feature. These allow you to execute different parts of your workflow sequentially. I've taken this approach in the attached workflow.

Hope this helps. Cheers!

community_response7.yxmd