Comparing 2 data sets to find missing and extraneous information

I have been trying to find an efficient way to compare 2 data sets to find missing or extra information, and was wondering if anyone would be able to help me out.

I have the below example. Dataset 1 is sort of a "key", where I have a set of values in column B that should be associated with a set value in column A. In dataset 2, I can have multiple records that contain the set value in column B, and then the values I want to compare are in column C. So I want to use the relationships of column A-B on Dataset 1 to compare to Column B-C on dataset 2. I have included my ideal output as well.

Help

Accepted answers

T_Willins

Hi @IMensink,

The Join tool is your friend for this. By Joining data and using the left and right unjoined rows you can find where each data set doesn't match the other. Additional data from Dataset 2 (Column A) must be added to Dataset 1 (Summarize and Join) to allow for all fields to be compared in the 2nd Join. The remaining tools rename, change field type, summarize, concatenate, and Join/Union the data together in the format you are looking for.

Compare 2 data sets.png

Compare 2 data sets.yxmd

All comments

T_Willins

Hi @IMensink,

Compare 2 data sets.png

Compare 2 data sets.yxmd

IMensink

Thank you @T_Willins, very helpful!

Meena

Hi @T_Willins

Please could you help me if this solution will work for my requirement which is similar to this.

I have to find the missing Type Group (rows) with the Lookup.

Lookup Table columns Seq Code, Type Code matched with the sample Data .. and any missing Type Group must be reported for each Seq# for the Seq Code, Type Code combination.

Sheet Unit_Test contain the example which is expected to be in output.

Thanks

Meena

Missing Roles_Sample Data.xlsx

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335