Compare two large excel worksheets with detail

[JTCairns]

Hi All,

I am currently working on re-doing a very manual excel process with Alteryx. I have been struggling to find an effective way of checking my results.

The end data set comes from combining several input data sets and ends up being ~80,000 records with 100+ columns, I need to be able to check my results (using old data) with a version that is correct from the current process. However I need to check each record in each column and see clearly where things have went wrong.

I have tried joins on joins etc but wondered if anyone had any innovative ideas? I also need to check EVERY record so sampling doesn't work.

Any ideas greatly appreciated

Many Thanks

[BenMoss]

I would tranpose each data stream then you only have to do a single join on the column name and 'recordID' before doing a formula to identify the difference.

This is the exact thing I am doing now.

This will give you an 8million row table but that should be fine for alteryx to handle.

[JTCairns]

Hi @BenMoss,

Thanks for the speedy reply, could you post an example please im not sure how this would work (transpose is not on e of my more used tools) 

Many Thanks,

[BenMoss · Accepted solution]

No problem, see attached.

[JTCairns]

Thanks a lot @BenMoss that has worked great, just one last thing how do i revert the transpose, im guessing cross tab but what fields etc.

You're my new best friend

[BenMoss]

To revert you should use the cross-tab as you mention.

Your key field would be your ID.

The Header field would be your 'name' column.

And the value field would be either of the value fields, depending on which you want to take through.

Ben

[ShantanuDagar]

Hey bro.

Does the formula in the filter tool you have added work on string matches too?

Like changes in the address field or name field?