how to get obervations having same combinations

[genetist]

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

[wenqq3]

Filter column B = x, and column Y = y.
That will be easy, if you wish change any other value.

[Fotis1991]

1 way could be this.

In D2 and copy down.

=SUMPRODUCT(($B$2:$B$11=B2)*($C$2:$C$11=C2))>1

Column D will be a helper & hidden column.

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

=IFERROR(INDEX(A$2:A$11;SMALL(IF($D$2:$D$11=TRUE;ROW(A$2:A$11)-1);ROW(A1)));"")

[:) Sixthsense :)]

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

[XOR LX]

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

[Fotis1991]

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.