Compare Two excel files for differences

[SouravKayal]

Hi Team, 

I have two files with data of a ID and Name and need to compare both to see any differences week over week

Table 1

ID                Name

513450         XYZ

513450         ABC

11111           6789AF

513130         XYZ

Table 2

ID                Name

513450         XYZ

513450         DEF

11111           6789AF

11111           ABC

513130         XYZ

513130         ABC

513450         ABC

the output should be

513450         DEF

11111           ABC

513130         ABC

i was trying with a record id and transpose , but dint work through. Greatly appreciate the help

[Luke_C]

Hi @SouravKayal 

Join the data together on both fields and review the L/R anchors for dropouts (changes):

[tkozlyk]

ok - but I am looking to compare every cell of 2 excel files with the same layout (field names & positions)

then must use transpose?

TDK

[Deano478]

@tkozlyk  You can transpose then join on the appropriate fields to find you differences  

[Deano478]

Hers is one way of doing it 

[tkozlyk]

Yes, thank-you !

I mocked up mine based on yours and it works well. In my CPI's with my current alteryx flows, I want to ensure the original o/p is not broken. Hence, I want to compare both excel files (previous to my CPI & after my CPI) - both excel files have same schemas - to ensure cell values are not changed.

Many thanks again !

[Deano478]

@tkozlyk Glad I could be of help and and that you were able to get your expected outputs 

[ilovecoding]

What if there are n number of rows and columns. What is the best way to compare each cells of two dataset to highlight the differences/changes?

[Deano478]

@ilovecoding you can tweak the setup of the transpose to accommodate for different columns before joining them 

[ChrisTX]

Here are some good resources for comparing two data sets:

Check out this Knowledge Base article

Community > Support > Knowledge > Designer > How To: Compare Data from Two Data sets

Compare every value in every field from 2 different inputs

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Compare-Data-from-Two-Data-s...

On the Gallery (Toolbox > Gallery at the top of this page) search for

CReW Delta (Alpha)

This macro allows a user to input two files that are expected to be equal. The user can select which fields are to be compared (BLOB and Spatial fields are not compared). The "Delta" output will include any differences.

Comparing Data from Two Sets of Data, Calling out Differences

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Comparing-Data-from-Two-Sets-of-Data-C...

Compare 2 Data Sets

https://community.alteryx.com/t5/Engine-Works-Blog/Compare-2-Data-Sets/ba-p/88853

Community > Getting Started > Alteryx Use Cases > Data Check Between Two Sources

https://community.alteryx.com/t5/Alteryx-Use-Cases/Data-Check-Between-Two-Sources/ta-p/270082

Combining 2 Data Sets and Highlighting Differences in Output

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Combining-2-Data-Sets-and-Highlighting...

Chris