+ Reply to Thread
Results 1 to 6 of 6

how to get obervations having same combinations

  1. #1
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    how to get obervations having same combinations

    Hi to all,
    I have data like this,
    mat F M
    p1 x y
    p2 a b
    p3 c d
    p4 x y
    p5 x y
    p6 e f
    p7 j k
    p8 x y
    p9 r r
    p10 x y

    now i want to get mat numbers having same F and M combinations, for example i am expecting to get p1,p4,p5,p8 and p10 because they have same F and M combinations. i need to work out this with formula because i have very big file contains lot of data like this. I am attaching sample file for better understanding. any help would be appreciated
    Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: how to get obervations having same combinations

    Filter column B = x, and column Y = y.
    That will be easy, if you wish change any other value.
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to get obervations having same combinations

    1 way could be this.

    In D2 and copy down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Column D will be a helper & hidden column.

    Then use this ARRAY formula in A14, copying down and across to get your results.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: how to get obervations having same combinations

    In A14 Cell

    =IFERROR(INDEX($A$2:$C$11,SMALL(IF(($B$2:$B$11="x")*($C$2:$C$11="y"),ROW($C$2:$C$11)-ROW($B$2)+1),ROW($A1)),COLUMN(A$1)),"")

    Drag it down and right...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: how to get obervations having same combinations

    Hi,

    Based on your sample data, enter this array formula in A14 and copy across and down until you start to get blanks for the results:

    =IFERROR(INDEX(A$2:A$11,SMALL(IF(TRANSPOSE(MMULT((TRANSPOSE(ROW(A$2:A$11)^0)),(--($B$2:$B$11&$C$2:$C$11=TRANSPOSE($B$2:$B$11&$C$2:$C$11))))>1),ROW(A$2:A$11)-MIN(ROW(A$2:A$11))+1),ROWS($1:1))),"")

    Regards
    Last edited by XOR LX; 09-11-2013 at 03:24 AM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: how to get obervations having same combinations

    Thank you for the rep.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Finding possible combinations & listing the wanted combinations
    By Zoke in forum Excel Programming / VBA / Macros
    Replies: 30
    Last Post: 07-16-2012, 03:41 PM
  2. All Possible Combinations
    By tlhood1080 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-10-2009, 11:44 PM
  3. Possible combinations
    By bobbak in forum Excel General
    Replies: 3
    Last Post: 02-22-2008, 05:31 PM
  4. [SOLVED] getting combinations
    By vecky in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-31-2005, 08:40 PM
  5. [SOLVED] Combinations
    By osprey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1