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:
zhshqzyc
  • 3
  • 2
1 Solution
 
barry houdiniCommented:
I'm not sure what is matching against what - can you spell it out? Why is O4 3?

regards, barry
0
 
zhshqzycAuthor 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
 
barry houdiniCommented:
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
 
zhshqzycAuthor Commented:
It is too hard to understand it. Could you please add more columns at the most right and split the steps?
 
0
 
barry houdiniCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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