Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 227
• Last Modified:

# Compare and count

Hello,

Please look at the attached file.
The yellow columns are group1, column G to N are group 2.
Group 1 has 3 pairs of normal and tumor.
Group 2 has 4 pairs of normal and tumor.
Now I add one more column "MTACH" to see whether Group 2 contains pairs in group 1. For example, row 2 in group 1 has pairs A1,A2,A3, group 2 contains A1,A2 and A3. Thus " MATCH" is 3.

Thanks for the formula.
Book1.xls
0
zhshqzyc
Asked:
• 3
• 2
1 Solution

Commented:
I'm not sure what is matching against what - can you spell it out? Why is O4 3?

regards, barry
0

Author Commented:
Well, we can understand it in this way.
For O4,
Group 1 can be thought as a string list
g1 = {AG_AG,AG_AG,AG_AG}
then
g2 = {AA_AA,GG_GG,AG_AG,AA_AA}
if using other language suh as C#
g2.contains(g1[0]) is true
g2.contains(g1[1]) is true
g2.contains(g1[2]) is true
Thus the result is 3.

0

Commented:
OK, this might be overly complex for your simple data but this would also be easily "extensible" for larger ranges of data. This formula in O2

=SUM(IF(RIGHT(A\$1:E\$1,6)="Normal",IF(ISNUMBER(MATCH(A2:E2&"-"&B2:F2,IF(RIGHT(G\$1:M\$1,6)="Normal",G2:M2&"-"&H2:N2),0)),1)))

confirmed with CTRL+SHIFT+ENTER and copied down

see attached

regards, barry
27425680.xls
0

Author Commented:
It is too hard to understand it. Could you please add more columns at the most right and split the steps?

0

Commented:
OK, try this version in O2 copied across to P2

=OR(A2&"-"&B2=\$G2&"-"&\$H2,A2&"-"&B2=\$I2&"-"&\$J2,A2&"-"&B2=\$K2&"-"&\$L2,A2&"-"&B2=\$M2&"-"&\$N2)+0

then in Q2 you sum those columns for the result, see attached

regards, barry
27425680v2.xls
0

## Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.