Compare and count

Posted on 2011-11-01
Last Modified: 2012-05-12

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.
Question by:zhshqzyc
    LVL 50

    Expert Comment

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

    regards, barry

    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}
    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.

    LVL 50

    Expert Comment

    by:barry houdini
    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


    confirmed with CTRL+SHIFT+ENTER and copied down

    see attached

    regards, barry

    Author Comment

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

    Accepted Solution

    OK, try this version in O2 copied across to P2


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

    regards, barry

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    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.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now