Isolating field discrepancies between records with the same ID?

[DataDaemon]

Hello All!

Curious if anyone knows of a way to isolate data discrepancies when record IDs are grouped and summarized? My apologies in advance because I don't know what a good output would even look like in the solution to this problem.

I am summarizing lists where I group by [Record Type] and use the “first” occurrence on a sorted sheet to ensure that I summarize to the parent data on my output – however, does anyone have a creative way to isolate what records have discrepancies of the fields being summarized? 

Say the below is the input file:

Table (A)

Record ID	Record Type	Name	Data	Billable
1	Parent	Jim Store Prime	abc	5
1	Child	Jim Store A	abc	5
1	Child	Jim Store B	def	10
2	Parent	Fred Store Prime	xyz	10
2	Child	Fred Store A	xyz	10
3	Parent	Hank Store Prime	nop	5
3	Child	Hank Store A	qrs	10

Turns into this:

Table (B)

Record	Record Type	Name	Data	Billable
1	Parent	Jim Store Prime	abc	20
2	Parent	Fred Store Prime	xyz	20
3	Parent	Hank Store Prime	nop	15

This is where I've hit a creativity mental block...

Is there a good/viable/creative way to call out the Child accounts in Table (A) that don't match the [Data] field of their parent (an example being Jim Store B [Data] is 'def' rather than 'abc')?  My goal is to be able to locate the Parent and Child [Record Type] accounts that have a different [Data] value to begin the process of correcting them in an external system.

I sincerely appreciate the help and insights you all provide on a daily basis! Thank you!

[ivoller]

Not sure I completely understand the requirement. See attached for an example flow though. If this is insufficient I'm confident that something can be done with the multi row formula tool.

Cheers,

Iain

[Joe_Mako · Accepted solution]

In your Summarize tool you could add a Count Distinct on your Data field, for example:

Then you can add logic to text for when that count is greater than one. In the attached, I used a Message tool.

[DataDaemon]

This is such a simple way to identify them in summary, thank you!

Do you think there is a way to have it point out which child is the "incorrect" one, like in the case of [Record ID] 1? 

[Joe_Mako]

A Join tool could pull those records out:

An you can use another Join to pull in the Parent data as well:

see the attached for details.