Find non-matching column values with multiple row different identifiers

Hello All,

I am looking at employees and their dependents. I want to validate that the dependent's values for each monthly column matches the employee's. Possible values for each monthly column are 0, 1, and 2.

ID1 = Employee SSN, ID2 = either Employee or Dependent SSN respectively.

Thank you in advance!

Validate_Monthly_Indicators.yxmd

Datasets

Help

Accepted answers

Luke_C

Hi @EricMartinson

Here's one approach that should work for any number of dependents.

Steps:

Isolate the main employee (ID1 Name = ID2 Name) for 'correct' values
Transpose main set of data and filtered set of data, and join
Run a formula to compare the values
Crosstab the data back

Validate_Monthly_Indicators.yxmd

Qiu

@EricMartinson
For the filter tool I used "Dep", you can change it according to your actual.

Validate_Monthly_Indicators-r1.yxmd

All comments

Luke_C

Hi @EricMartinson

Here's one approach that should work for any number of dependents.

Steps:

Isolate the main employee (ID1 Name = ID2 Name) for 'correct' values
Transpose main set of data and filtered set of data, and join
Run a formula to compare the values
Crosstab the data back

Validate_Monthly_Indicators.yxmd

Qiu

@EricMartinson
For the filter tool I used "Dep", you can change it according to your actual.

Validate_Monthly_Indicators-r1.yxmd

EricMartinson

Thank you @Luke_C , this works perfectly!

EricMartinson

Thank you @Qiu, this is great!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703