Compare Rows of Data from 2 files.

[Charlie_J]

Hey Guys,

I have 2 excel files containing sales information and i need to perform a reconciliation to make sure they match 100%. I need a workflow that will compare every row from 1 file against every row from the other file and isolate any row that only appears in 1 of the files. I have each file coming in from a different input and have attached examples of what my data looks like. I also import each file with the full file path so i know where each row of data came from. The bottom 2 rows in example 2 do not appear in example 1. Typically these files will contain hundreds of rows so i need a way to isolate unique rows. Any help is much appreciated. 

[IraWatt · Accepted solution]

Hey @Charlie_J,

You can use the join tool to compare all the rows from each data set:

(shown by the arrow) I joined on every column I wanted to compare (in this case all of them). Any row which is unique to the left file will appear in the left output of the join. Any row which is unique to the right will appear in the right output. 

How many columns will your data set have?

Any questions or issues please ask :)
HTH!
Ira

[Jotigautam]

you can use join tool to get the rows that don't appear in both files and use another join tool for each file to exclude those rows and use append tool on the rest of the rows to reconcile.

[cvalteryx]

Hey @Charlie_J ,

This might help too!

[grazitti_sapna]

Hello @Charlie_J, there is another way of solving this problem in case you have hundreds of columns to match then you can use this workflow if it fits your scenario.

In case you have something specific please let us know.

Thanks!

[Charlie_J]

This worked perfectly, thank you so much. 

[grazitti_sapna]

@Charlie_J, can you please try my workflow as well if it fits your requirement?

Thanks!

[IraWatt]

No worries @Charlie_J 😄, I would look at @grazitti_sapna solution also. It works a lot better with data with a lot of columns.