Checking if rows belonging to same group are subset of another group

[sehgalsanchit92]

Hi, I am breaking a sweat on this. I have a data set like below. I want to insert a column mentioning the comment "Present in Roll No 123 or 456' if all the subjects of one roll no is present in the other roll no. 

Example: Roll No 123 has Subjects 'H' & 'Z'. Now I want to check if both 'H' & 'Z' are part of some other roll no. In our case, Yes as Roll No 912 contains 'H' & 'Z' subjects along with other subjects. So i want to write a comment in front of Both rows of 123 "Present in 912"

Example 2: roll 912 has subjects F, H, L, Z which are also present in Roll no 765 which has 6 subjects (so roll no 912 is subset of roll no 765). I want to write "Present in 765" as comment against all 5 rows of 912.

Roll No	Subject	Count of Subject
123	H	2
123	Z	2
912	F	4
912	H	4
912	L	4
912	Z	4
765	B	6
765	F	6
765	G	6
765	H	6
765	L	6
765	Z	6

Please help me with this use case. I am stuck with this

[binuacs]

@sehgalsanchit92 H &Z also present in 765, how to handle that part

[sehgalsanchit92]

Then it should insert comment as "Present in 765". Priority would be given to higher count.

[CoG · Accepted solution]

You can do a self Join to identify all similarities and then choose the smallest potential parent group as the official parent (this can be modified to achieve different rules based on which subsets take priority):

Hope this helps and Happy Solving!

[sehgalsanchit92]

Thanks a ton! I have tried this solution but I have more than 5 lakh rows, so testing out things. Would confirm you back soon!

[sehgalsanchit92]

CoG : Thanks a ton! Your solution has helped me solving the problem in minutes.