Comparing 2 big data sets with similar columns.

[Conner97]

Hello,

I am trying to compare various columns from 2 separate workbooks, based upon a shared Asset_ID column in both workbooks. Both excel files contain different information and are presented differently, but they do share 14 columns with the same information that are named the exact same, all based upon the same Asset ID#. My goal is to bring in the Asset ID #s that they share, then compare the 14 columns from the first file, to the same 14 files from the second file, but they are located in different spots in the excel files. I would like to show only the same 14 columns they share based upon their shared Asset IDs, and then show any variances between the 14 columns. I appreciate any help!

[ChrisTX]

Have you tried using two Input Data tools, and a Join tool? 

If the Asset ID# isn't the same data type, try a Formula tool or a Select tool to update the data type.

For calculating variance, you could use 14 formulas, or try a Transpose then Multi-row formula, then Crosstab.  Or a batch macro.

Please post sample input data and expected output.

Chris

[griffinwelsh]

Use a join tool on Asset ID to get a list of asset ids for consideration. You can select the columns by name for your output from the join. Then you can use a formula tool to compare the values between each column pairing.