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
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 * +++
1 way could be this.
In D2 and copy down.
Formula:
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:Please Login or Register to view this content.
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.
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
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks