Compare between 2 files , and create new file with sheet names Trade, Document, Difference

[BRRLL99]

Hi Team,

I have 2 files which i need to be compared and create new sheet in Alteryx, which needs to be joined on the basics of ISIN column

expected output file has been attached at end I'm looking for all sheets need to be in one file ( Trade, Document, Difference)

 if background highlight is not possible at least mark difference amount in red color

File : 1 ( Trade)

Name	Slno	ISIN	Col_Volume	Col_Amount
HK-01A	1	619K	100	10
HK-01A	2	629K	200	30
HK-01A	3	639K	300	50
HK-01A	4	649K	400	70
HK-01A	5	659K	500	90
HK-01A	6	669K	600	110

File : 2 (Document)

ISIN	Col_Volume	Col_Amount
619K	100	10
629K	200	88
639K	800	50
649K	400	70
659K	500	33

[Hammad_Rashid]

1. Input Data:

   • Read both files ("Trade" and "Document") into Alteryx using the Input Data tool.

2. Join Tool:

   • Use the Join tool to combine the two datasets based on the "ISIN" column.
   • Configure the Join tool to join on the "ISIN" column.

3. Select Tool:

   • Use the Select tool to choose the columns you want to include in the output.

4. Formula Tool for Difference:

   • Add a Formula tool to calculate the difference between corresponding columns from both files.
   • For example, if you want the difference in "Col_Amount," the formula would be something like: [Col_Amount_Trade] - [Col_Amount_Document].

5. Filter/Conditional Formatting:

   • If you want to highlight the differences, you can use the Filter tool to separate rows with differences.
   • For conditional formatting (like red color), you can use the Multi-Row Formula tool to add an HTML tag for styling based on the calculated difference.

IF [Difference] < 0 THEN "<span style='color:red'>" + STR([Difference]) + "</span>" ELSE STR([Difference]) ENDIF

6. Union Tool:

   • Use the Union tool to combine the original data with the calculated differences.

7. Output Data:

   • Finally, use the Output Data tool to save the combined and modified data to a new sheet or file.

[BRRLL99]

How can create new file

with sheet names >> Trade, Document, Difference

[caltang]

Like so:

[BRRLL99]

Thank you for your response

but my output should have 1 excel file and 3 sheets in it.

Trade(sheet-1) , Document(Sheet-2), Difference(Sheet-3)

[caltang · Accepted solution]

Like so:

@DataNath wrote a great article here on how: https://community.alteryx.com/t5/Engine-Works/How-to-Render-to-Multiple-Named-Excel-Sheets/ba-p/1015...

If you want to further customize the look and feel, refer here:

• https://community.alteryx.com/t5/Engine-Works/How-to-Create-Merged-Cells-in-Reports-Part-1/ba-p/9746...
• https://community.alteryx.com/t5/Engine-Works/How-to-Create-Merged-Cells-in-Reports-Part-2/ba-p/9787...
• https://community.alteryx.com/t5/Engine-Works/How-to-Create-Merged-Cells-in-Reports-Part-3/ba-p/9813... 

[Qiu · Accepted solution]

@caltang 
Thank you. I was struggling with how to assign the Sheet name with Render tool. Learned new thing from you.

@BRRLL99 
I have similar approach with @caltang with slight difference on the Column Rule using.

[caltang]

The honour is mine @Qiu - your method is cleaner!

[rohit782192]

You can use Spreadsheet compare Tool which is available readily in Windows 11 for Comparison of File.