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

[Pragya_Shree]

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:

Id	Name	Country	City	Work
01	John	US	NewYork	IT
01	John	US	Michigan	IT
02	Sam	Australia	Sydney	Radio
02	Love	Australia	Sydney	Radio

Result:

Id	Name	Country	City	Work
01	John	US	NewYork	IT
01	John	US	Michigan	IT
02	Sam	Australia	Sydney	Radio
02	Love	Australia	Sydney	Radio

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 solution]

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 solution]

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?

[Pragya_Shree]

@jonathan Thank you so much! it worked perfectly with a large amount of data.