How To Find Subsets In excel?

not sure what you have but it's usually better to start with everything in a separate cell

yeah i placed each entry of s1 in seperate cell and same with s2 and s3

data is placed in following fashion

A1 -> s1
B1 -> v1
C1 -> v2
D1 -> v3
E1 -> v4
F1 -> v5

A2 -> s2
B2 -> v3
C2-> v5

A3 -> s3
B2 -> v3
C3 -> v7


s1 is master and how can i find s2 as subset of s1 and s3 in not using a logic in excel (not manually)?????? plz help

Originally Posted by martindwilson

not sure what you have but it's usually better to start with everything in a separate cell

Assuming that all of your data is in columns B through F, you could insert the following formula in cell G2:

=IF(SUM(IF(ISERROR(HLOOKUP(B2,$B$1:$F$1,1,FALSE)),0,1),IF(ISERROR(HLOOKUP(C2,$B$1:$F$1,1,FALSE)),0,1),IF(ISERROR(HLOOKUP(D2,$B$1:$F$1,1,FALSE)),0,1),IF(ISERROR(HLOOKUP(E2,$B$1:$F$1,1,FALSE)),0,1),IF(ISERROR(HLOOKUP(F2,$B$1:$F$1,1,FALSE)),0,1))=COUNTA(B2:F2),"Yes","No")

You can then drag the formula down for subsequent rows.

This formula works by comparing the sum of the items found to the sum of the items looked for, and returns a yes if they are equal. You can expand this as far as you want by adding other terms to the sum and expanding the hlookup and counta ranges accordingly. Note that if there is no data to check, the formula returns a "yes" because 0=0. This could be adjusted if you want.

Sorry for resurrecting a year old post. I saw the challange and couldn't resist. I hope this helps someone.