Filter or multi row formula help

[markharris]

Hi Experts,

I have this table below, what I need to do is only retain those with duplicate registry ID and unique country. Is this possible in filter or multi row formula?

Registry ID	Country
12448	ES
12448	AU
12514	ES
1013030	CZ
1026241	CZ
1026243	HU
1026243	US
1027204	AE
1027244	SE
1027320	IT

The desired output should be:

Registry ID	Country
12448	ES
12448	AU
1026243	HU
1026243	US

[Felipe_Ribeir0]

Hi @markharris 

One way of doing this

[Prometheus]

You can use a Summarize tool to group by Registry ID and then count Registry ID. After that, bring filter out every record that has a count of 1, then join it back to the original data to get your desired output.

 

 

[markharris]

what if you use this data instead. The output should be null. Yet it still posted the same output. The logic is to find duplicate registry ID then check for a unique country. If the country is the same it should omit it.

12448 ES
12448 ES
12514 ES
1013030 CZ
1026241 CZ
1026243 HU
1026243 HU
1027204 AE
1027244 SE
1027320 IT

[Felipe_Ribeir0 · Accepted solution]

Hi @markharris 

Just change the summarize tool to count distinct countries by registry ID:

[Prometheus]

I added basically the same logic but for Country and used CountDistinct instead. Then I joined it back to the original data and got zero records.

 

 

[markharris]

Thank you for taking time to assist. 🙌