Comparing 2 Spreadsheets for Differences

Apologies as I'm sure this question has been asked so many times. I am trying to build a business case for the need to get Alteryx, and I am just looking for some advice on which tool(s) I would need to use to compare 2 spreadsheets to highlight any differences in any cell in both data sets. I have figured out how to sort the 2 different inputs so the columns are lined up. I'm just looking for a way to now compare the 2 sheets for any differences in any cell. I have attempted using the Join tool but not sure if that is producing the result I want.

Any help would be greatly appreciated.

Regards

Common Use Cases

Accepted answers

fharper

@jmmart08 Since you are new I want to point out that when someone provides you a solution you are satisfied with you have the ability, since it is you post, to mark that response as the or A solution....sometimes there are more than one viable solution. This is important so the people looking at the problem know so they either do not continue to contribute or if they were following to get a solution know what works. helps other months from now find the solution also.

@JosephSerpis did what I would have done as well so I am confident that will be a solution.

@AndrewS had a solution as well using a CReW macro...there is often more than one solution...

That said that task is a drop in the ocean in respect to all that Alteryx can and will do for your organization. When I was first introduced to Alteryx it was just 2 of us, 2 licensees, and in a few months we did so much we became the go to people to get rapid and accurate reporting and analysis on many things within our department...within a year other departments started acquiring licenses because they could do more and faster with same people.

Many orgs benefit because it provides a measure of independence from IT because the business experts with a little software savvy can solve a lot of issues themselves. for people who are developers they can do many things faster so ad-hoc and analytics is much more efficient/effective compared with typical tools best suited for conventional development.

Just get smart fast on implementing a bit of SDLC, look it up. You will blink and before you know you have hundreds of workflows with 10 or 15 people touching them and some becoming "productionized" and then there are macros for reusable code....you don't want bunches of different versions of flows and macros floating around on local drives plus shared drives. Discipline and a strategy is key.

Enjoy the ride though...Alteryx is a great tool.

All comments

JosephSerpis

Hi @jmmart08 I mocked up a workflow that demonstrates the approach I've taken to this task before. Essentially you want to use the Transpose tool to pivot your data so it's easier to use the Join tool and compare against all the values of your spreadsheet. Let me know what you think?

Differences_180919.yxmd

jmmart08

Hi JS420,

Thank you VERY much! I really appreciate this. I am so new at this that I don't really understand what the Record ID piece is doing, and also in the Join tool where at the top you have Name on the Left, Name on the Right, and RecordID on the Left and RecordID on the right. For example, my column headers are TD, Broker, UNITS, etc. Would I place all of those fields as the left and right entries? I also clearly don't understand the bottom portion in the join tool either. I do not know which of the selections to check or uncheck. Any explanation you may be able to give me would be extremely helpful and extremely appreciated.

Thanks again!

AndrewS

Hi @jmmart08

You could also try the Crew macro 'Expect Equal' It will tell you if columns have different names/are missing and if cell data is not in sync.

expectequal.yxmd

fharper

@JosephSerpis did what I would have done as well so I am confident that will be a solution.

@AndrewS had a solution as well using a CReW macro...there is often more than one solution...

Enjoy the ride though...Alteryx is a great tool.

jmmart08

Hi Joseph

Thanks again for your workflow. Works great but I am running into an issue with the data I have. So in the workflow you so generously created for me, you've got Products and Sales as basically your row headers, and then their accompanying values. Makes perfect sense to me. Record 3 of the sales "Header/Name" value on Sheet1 differs from the Record 3 value of the sales "Header/Name" of Sheet 2.

I have basically 4 more "Headers" in my data, and let me try to explain what is happening. Using the sample you provided to me, let's say that you had 2 additional rows of data in which the values are 50A and 50B. Let's say those match in both spreadsheets, the same RecordID on both sheets shows sales of 50A for let's say Product D and the same for sales of 50B. If I was to switch say spreadsheet 2 to show Product D Sales value of 50B but have spreadsheet 1 show Product D Sales value of 50A, this should show a difference correct?

The issue I am having is I again I have 6 total "columns" that I am trying to match values for on all. I have scenarios where the biggest error I am really trying to account for is the individual that enters into 2 different systems manually has a tendency to flip (Using real data for me now) Accounts that are A and B. For example, 402A is entered in 1 system and then 402B is entered in the other system. But both of these accounts exist in each system so 402A and 402B are both valid entries but they're reversed from 1 system to the next. I am not getting any difference (I have added formulas, filters, etc to parse down my data to get each system exact) at the end. So 1 of my columns, quantity, is clearly off and sheet 1 doesn't match sheet 2 and the same would go for the amount. But I come out with matched still at the end. Any ideas you have? Apologies for revisiting this as it has been extremely helpful!

Rutujabothara

Hi,

I am having two sheets, 1 have 10 static columns and other sheet have 3 columns ( these can vary and be upto 10 columns as in sheet 1). I have joined the data but now i want a difference column for each column present in 2nd sheet.

How can i compare columns in both sheets ? I want a difference columns for only the columns in 2nd Sheet (3 columns).

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703