How to compare two excel files and highlight the differences

I couldn't find what I'm trying to achieve elsewhere on the board. Would appreciate any help.

I have two excel files.

Each file contains a series of columns would like to concatenate and then search throughout the 2nd file to see if that combination of columns exist.

I would like to see an output that identifies which records were not in the 2nd file and vice versa. (which records were in the 2nd file, but not in the first.

Could someone explain to me how to create such a workflow, please?

Thank you

*Update - the attached file contains sample data. I've made 2 tabs to represent both files.

I'm using Designer 2022. Ideally, I'd like a solution where I don't have to concatenate, sort the records or add a Record ID.

alteryx query file 2.xlsx

alteryx query file 1.xlsx

Workflow

Join

Alteryx Editions

Accepted answers

All comments

jrlindem

For comparing the columns, I have used the following approach:

Use the FIELD INFO tool to grab the list of fields from each dataset
SORT them both alphabetically
Add a RECORD ID
JOIN the two datasets on the Record ID
Use a FORMULA and/or SUMMARIZE tool to compare the lists and count the number of records for each

This should give you a good sense of columns from both datasets.

For the data itself, assuming your columns test passes, your observation of concatenating fields across columns and then using formulas to compare the values is often suggested in these discussions as a viable solution.

Hope that gives you some inspiration to build out a solution. Otherwise, I recommend attaching an example pair of datasets and I’m sure someone could consider building out a proof-of-concept.

Cheers, -Jay

OTrieger

@noname1234

I will not use the RecordID as you do not know if the orders of the field is the same in both files, and then by matching RecordId you might get wrong matching, just do the above without using the RecordID just match the names of the fields and then if the fields are in different order you still will get what you want.

Even if you will sort the names, if a field is missing you will get wrong matching as RecordID will give you the current sequence.

Just to give example:

File: A

Name

Salary

Pension

Bonus

Tax

File B;

Name

Salary

Pension

Tax

If you will use RecordID Bonus will be 1 in file A will Name will be 1 in file B

Gaurav_Dhama_

Which version of Designer are you using?

if you are 0n 2024+, try this workflow. You have an inbuilt tool that you can utilize.

Checking different data.yxmd

jrlindem

For the portion I was detailing around comparing columns, the step of sorting them alphabetically before adding Row_ID is key to getting them in the same order. If you have 4 columns in one file and 5 in the other, though, you have a discrepancy! You can also consider using JOIN and seeing what falls out if that helps you avoid this approach.

Also, keep in mind that my response before, is merely guidance. You'll need to consider how and why those tips may be of use and how to adapt them to your case, but since we can't see the data, it's hard to be more specific.

Looks like there are some other good responses here too. I hope you get what you need! -Jay

noname1234

Thanks for the help, Jay.

Is there a way to do this without adding a Record ID?

I've attached a sample pair of data to my original post..

noname1234

currently on 2022, but will be switching to 2024 after Frequency Table/Summary becomes available. I'll def. try this out when i make the switch though, thanks Gaurav

would it be possible to send screenshots of what you created though?

caltang

Blast from the past: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Comparing-2-Spreadsheets-for-Differences/td-p/465986

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703