Comparing rows values for multiple values and highlight unmatched col values.

Question

Problem: I have a scenario where I have around 50 columns and one ID column( not unique), hence we group by the data on the id column then suppose I have 5 rows for the same id.  Now I need to make sure that the rest of all the 50 columns except id col should have similar data across respective columns if in any row a column value is not as per requirement then it needs to be highlighted.

Example:

IdNameCountryCityWork01JohnUSNewYorkIT01JohnUSMichiganIT02SamAustraliaSydneyRadio02LoveAustraliaSydneyRadio

Result:

IdNameCountryCityWork01JohnUSNewYorkIT01JohnUSMichiganIT02SamAustraliaSydneyRadio02LoveAustraliaSydneyRadio

Note: I know we can use Multi-row but this tool can only be managed for a few columns I need the same thing for more columns at once.

Thanks in Advance.

Jonathan-Sherman · Accepted Answer

Hi @Pragya_Shree,

An approach with pivoting and determining the mode (most common) value for each group in each column would work here:

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

Jonathan-Sherman · Accepted Answer

The difficulty in this case comes where you only have two records (so there is no way to easily distinguish the mode), however I would think in your dataset you have a large number of records so this wouldn't be an issue?