Compare two data sets & highlight the difference

viral_gada

Hi,

I am quite new to Alteryx.

I need to compare 2 datasets with same headers & also have a unique ID column which is same in both the files.

I require all the records in the output file with the changes highlighted in single color.

I have attached sample file with expected output tab as well.

Note: In sample file I have included few columns but actually I have 42 columns.

Can someone please guide me here. Thanks in advance !

Source Data.xlsx

Output

Common Use Cases

Join

Accepted answers

mceleavey

Hi @viral_gada ,

In your original post you are showing the output changing to version B, are you saying you don't want that?

I have amended this to show the original A input values and the mis-matched B input values.

I would advise having a look at the following resources which should help you understand the functionality of the Transpose and Crosstab tools:

https://help.alteryx.com/20223/designer/transpose-tool#:~:text=The%20Transpose%20tool%20is%20the,fields%20that%20can%20be%20transposed.

https://help.alteryx.com/20223/designer/cross-tab-tool

These should get you started on how to apply this functionality.

I've attached the latest workflow.

I hope this helps,

Comparison_mceleavey.yxmd

All comments

mceleavey

Hi @viral_gada ,

We're not using Excel here so we wouldn't be colouring cells, but I've quickly put together a workflow to show you how to do comparisons of this nature.

I've essentially transposed the data and brought the two sources together to compare the like for like values. I've then provided a simple formula which replaces the Source A value with the Source B value and marked the column that had the difference.

Workflow attached.

I hope this helps.

Comparison_mceleavey.yxmd

viral_gada

Thank you @mceleavey for the quick response.

I feel you resolved most of the part of what I was expecting.

I request if you could make below additional changes in the workflow & reshare the workflow, that would be great !

I need the output file same as the input file as the current output is adding more number of rows.

The data need not change ex: In check column, apart from same records, i see the value of A-Value has changed. I am attached the screenshot for the same.

I am happy to see the new column check & in that I can see what the changes was made in file B. (consider file A as a base file)

Once again Thank you for all your help here !

RobertOdera

Here you go @viral_gada

This should be adaptive to incremental dynamic columns.

Please mark it as a solution if it works for you. Cheers!

For_ViralGada.yxzp

viral_gada

Happy @RobertOdera for taking time.

The workflow shared by @mceleavey helped me to resolve my issue.

Thanks for your suggestion !

07igoej

Hi, I’m trying to download your workflow - but input files are not found when running. Do you know how to rectify? Thanks, James

sydnysak

Hi,

I have a similar query, would require your assistance

Activity - I download 2 Data sets, one containing data up until last Week Friday and the other data set up until Current Week Friday (Data is extracted for rolling 12 months) and compared on a weekly basis to identify if there were any new entries that have been logged since last week Friday until this Week Friday also , In the Rolling 12 Months as of last week and as of the Current week, i identify if there have been any changes in the values such as Status change, $ has increased or Decreased and so on.

Can someone guide me as to what are the tools that are required to automat this activity which is being performed on a weekly basis

saurabhkunal12

Hi, My requirement is to compare two excels which has same number of columns around 50. There is only one atribute which will be same for both the excels which is id . If any column which gets changed in pre release and post release file we need to highlight the difference in red colour and any new row added it will be complete new row for all 50 values will need in yellow colour. If suppose entire column value in post validation file is removed then everthing in pre release file will show as red and outputted.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703