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

[adarsh2608]

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.

[ChrisTX]

This seems like a very basic data prep task.  What have you tried so far?  Can you post a sample workflow?  Or do you just not know where to start?

At the top of this page, have you looked at the basic training under Learn > Academy > Learning Paths and Interactive Lessons?

[LindonB]

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....

1. Use a Block Until Done tool but this has limited uses as it assumes the same incoming data table.
2. 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).
3. 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!

[adarsh2608]

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  

[rohit782192]

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