Compare Rows of Data from 2 files.

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.

Example 1.xlsx

Example 2.xlsx

Input

Output

Tips and Tricks

Custom Tools

Expression

Data Investigation

Accepted answers

IraWatt

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

Workflow.yxmd

All comments

IraWatt

Hey @Charlie_J,

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

How many columns will your data set have?

Any questions or issues please ask
HTH!
Ira

Workflow.yxmd

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

Charlie_J

This worked perfectly, thank you so much.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235