Saturday, March 5, 2016

yogi_Pull Into Sheet7 Names That Atre Present In Column C Of Both Sheet5 And Sheet6

Google Spreadsheet   Post  #2048
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-05-2016
post by: Tom Kalinoski:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/g0x__YAVhv4;context-place=topicsearchin/docs/lists

Get list of people that are in both sheets

I have a spreadsheet that contains a list of names in sheet 5 and another list of names on sheet 6.  I'd like to list all the names in sheet 7 that occur both in sheet 5 and sheet 6.  Meaghan Garufi is present once in sheet 5 and once in sheet 6.  She correctly shows up in sheet 7. Lenna Newville appears in sheet 5 two times and does not appear in sheet 6 at all.  Yet, she shows up in sheet 7.  She should not show up in Sheet7.  What is wrong with my formula.  I'm not married to my formula, so I am ok with changing it completely if there is a better way to accomplish this.

Thanks
Tom

=iferror(query(query(ArrayFormula({filter(ArrayFormula({Sheet5!A2:B,if(len(Sheet5!A2:A),1,)}),Sheet5!A2:A<>"");filter(ArrayFormula({Sheet6!A2:B,if(len(Sheet6!A2:A),1,)}),Sheet6!A2:A
<>"")})," select Col1,Col2,count(Col3) where Col1 <> '' group by Col1,Col2 label count(Col3)'' ")," select Col1,Col2 where Col3>1 ",0),"No matches found")
-------------------------------------------------------------------------------------------