Solved

# Compare and count

Posted on 2011-11-01
226 Views
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
Question by:zhshqzyc

LVL 50

Expert Comment

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

regards, barry
0

Author Comment

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

LVL 50

Expert Comment

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 Comment

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

0

LVL 50

Accepted Solution

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

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.