Find non-matching column values with multiple row different identifiers

[EricMartinson]

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!

[Luke_C · Accepted solution]

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

[Qiu · Accepted solution]

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

[EricMartinson]

Thank you @Luke_C , this works perfectly!

[EricMartinson]

Thank you @Qiu, this is great!

[Qiu]

@EricMartinson 

Happy to help and thank you for the accept mark. 😁