Analysis

[Ayaanarjun-5885]

Hi,

I have 5 legal companies with Country of incorporation, Domicile and legal country. I have to find out the Duplicate and unique companies based on the attributes(COI,COD,Legal country). To get the duplicates, atleast one value of attribute should match with other among the all the companies.If any of the value not matching with the other attributes then they are unique.

Conclusion	Duplicate(Matching COI with B)	Duplicate(matching COD with A)	Unique(Not matching with anything)	Duplicate(matching COI with A and B)	Unique(Not matching with anything)
Legal Company	A	B	c	D	E
COI	US	CA	GB	US	IE
COD	US	US		GB	CA
Legal country	CA	CA	US	IE	GB

[peterr_h · Accepted solution]

Hi @Ayaanarjun-5885,

I've attached an example solution for you here.

There is a formula tool with a long comment attached to it - the formula in here checks for matches based on various conditions. From what I gathered, you only want to flag where countries match for corresponding items (i.e. two COIs matching counts, but a COI matching with a COD does not count). If this is not correct, then this will be a simple change in the formula tool.

Please let me know if you need any help, but this should give you the solution you require, even if it requires a bit of tweaking.

The upper container is just ensuring that each match is unique - i.e., it isn't flagging both A matching to B on COD and B matching to A on COD (as these are the same thing, just reversed). This is a dynamic solution so will work/scale for whatever incoming dataset you have.

Let me know if you need any further help!

Cheers,

Peter

[Ayaanarjun-5885]

Hi @peterr_h 

Thank you so much for the below. It Worked to my data. I also wanted to add one more field to the below data "Cluster ID".

I wanted to perform the below analysis within the same Clusters as per the below. I have 3 different clusters below, so I wanted to perform analysis within each cluster for all the records to get output. Could you please help me in grouping by Cluster ID and perform analysis. I have 3 sets such as (A,B), (C,D,E,F,G),(H,I,J). Analysis should be among the records in those clusters only.

Thanks In Advance.

Company Name	Cluster ID	COI	COD	Legal country	Classification
A	003284297		GB	GB	Not Duplicates
B	003284297	US	US	MQ	Not Duplicates
C	003269552	CA	CA	CA	Duplicates
D	003269552	US	[Null]	US	Not Duplicate
E	003269552	CA	CA	CA	Duplicates
F	003269552	CA	CA	CA	Duplicates
G	003269552	CA	CA	CA	Duplicates
H	003294759	IE	IE	IE	Not Duplicate
I	003294759	GB	GB	GB	Duplicates
J	003294759		GB	GB	Duplicates